If startdate <> " " Then
Sql_text = Sql_text & " And FDate>=" & " '" & startdate & " '"
End If
If enddate <> " " Then
Sql_text = Sql_text & " And FDate<=" & " '" & enddate & " '"
End If
Sql_text = Sql_text + " group by fitemid,fnumber,fname,flowlimit,fhighlimit,fqty Order by fnumber "
Sql_text = Sql_text + " union select *,0 As fsellqty from V_XMK_SecInvQty where fitemid not in (select fitemid"
Sql_text = Sql_text + " from V_XMK_V_XMK_SecInv_sell_Qty where 1=1 "
If startdate <> " " Then
Sql_text = Sql_text & " And FDate>=" & " '" & startdate & " '"
End If
If enddate <> " " Then
Sql_text = Sql_text & " And FDate<=" & " '" & enddate & " ')"
End If
监视Sql_text的值,只能到"Select fitemid,fnumber,fname,flowlimit,fhighlimit,fqty,sum(fsellqty) as fsellqty From V_XMK__SecInv_sell_Qty Where 1=1 And FDate>= '2013-01 ' And FDate<= '2013-06 ' group by fitemid,fnumber,fname,flowlimit,fhighlimit,fqty Order by fnumber union s
7 个解决方案
#1
字符串的长度很长的,不止这些,是你显示值的框显示不下吧?
#2
显示框没问题的,看过了,跟踪执行sql就不对。
#3
Sql_text在哪里定义的?如何定义的?
是不是“dim Sql_text as string*255” ?
是不是“dim Sql_text as string*255” ?
#4
Dim Sql_text As String
Private Sub CommandButton1_Click()
Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim Sql_text,row1, startdate, enddate, Sql_text2, sqlstr As String
Dim i, j, k As Integer
startdate = Worksheets("sheet2").Cells(1, 5).Rows.Value
enddate = Worksheets("sheet2").Cells(1, 7).Rows.Value
cnnstr = "Provider = SQLOLEDB;" & _
"Data Source = 10.70.1.251;" & _
"Initial Catalog = AIS20120607184516;User ID =mro;Password =mro;"
cn.Open cnnstr
Sql_text = "Select fitemid,fnumber,fname,flowlimit,fhighlimit,fqty,sum(fsellqty) as fsellqty From V_XMK__SecInv_sell_Qty Where 1=1 "
If startdate <> " " Then
Sql_text = Sql_text & " And FDate>=" & " '" & startdate & " '"
End If
If enddate <> " " Then
Sql_text = Sql_text & " And FDate<=" & " '" & enddate & " '"
End If
Sql_text = Sql_text + " group by fitemid,fnumber,fname,flowlimit,fhighlimit,fqty Order by fnumber "
Sql_text = Sql_text + " union select *,0 As fsellqty from V_XMK_SecInvQty where fitemid not in (select fitemid"
Sql_text = Sql_text + " from V_XMK_V_XMK_SecInv_sell_Qty where 1=1 "
If startdate <> " " Then
Sql_text = Sql_text & " And FDate>=" & " '" & startdate & " '"
End If
If enddate <> " " Then
Sql_text = Sql_text & " And FDate<=" & " '" & enddate & " ')"
End If
rst.Open Sql_text, cn, adOpenStatic, adLockBatchOptimistic
Private Sub CommandButton1_Click()
Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim Sql_text,row1, startdate, enddate, Sql_text2, sqlstr As String
Dim i, j, k As Integer
startdate = Worksheets("sheet2").Cells(1, 5).Rows.Value
enddate = Worksheets("sheet2").Cells(1, 7).Rows.Value
cnnstr = "Provider = SQLOLEDB;" & _
"Data Source = 10.70.1.251;" & _
"Initial Catalog = AIS20120607184516;User ID =mro;Password =mro;"
cn.Open cnnstr
Sql_text = "Select fitemid,fnumber,fname,flowlimit,fhighlimit,fqty,sum(fsellqty) as fsellqty From V_XMK__SecInv_sell_Qty Where 1=1 "
If startdate <> " " Then
Sql_text = Sql_text & " And FDate>=" & " '" & startdate & " '"
End If
If enddate <> " " Then
Sql_text = Sql_text & " And FDate<=" & " '" & enddate & " '"
End If
Sql_text = Sql_text + " group by fitemid,fnumber,fname,flowlimit,fhighlimit,fqty Order by fnumber "
Sql_text = Sql_text + " union select *,0 As fsellqty from V_XMK_SecInvQty where fitemid not in (select fitemid"
Sql_text = Sql_text + " from V_XMK_V_XMK_SecInv_sell_Qty where 1=1 "
If startdate <> " " Then
Sql_text = Sql_text & " And FDate>=" & " '" & startdate & " '"
End If
If enddate <> " " Then
Sql_text = Sql_text & " And FDate<=" & " '" & enddate & " ')"
End If
rst.Open Sql_text, cn, adOpenStatic, adLockBatchOptimistic
#5
你在VBA里试一下,能赋值超过255个字符吗?
#6
没看清楚是VBA,刚刚打开excel试了一下
Private Sub CommandButton1_Click()
Dim s As String
Dim t As String, p As String
t = "[" & String(200, "t") & "]" & vbCrLf
p = "[" & String(300, "p") & "]" & vbCrLf
s = t & p
MsgBox s & vbCrLf & " len=" & Len(s)
End Sub
#7
应该是监视窗口只能显示255字符
#1
字符串的长度很长的,不止这些,是你显示值的框显示不下吧?
#2
显示框没问题的,看过了,跟踪执行sql就不对。
#3
Sql_text在哪里定义的?如何定义的?
是不是“dim Sql_text as string*255” ?
是不是“dim Sql_text as string*255” ?
#4
Dim Sql_text As String
Private Sub CommandButton1_Click()
Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim Sql_text,row1, startdate, enddate, Sql_text2, sqlstr As String
Dim i, j, k As Integer
startdate = Worksheets("sheet2").Cells(1, 5).Rows.Value
enddate = Worksheets("sheet2").Cells(1, 7).Rows.Value
cnnstr = "Provider = SQLOLEDB;" & _
"Data Source = 10.70.1.251;" & _
"Initial Catalog = AIS20120607184516;User ID =mro;Password =mro;"
cn.Open cnnstr
Sql_text = "Select fitemid,fnumber,fname,flowlimit,fhighlimit,fqty,sum(fsellqty) as fsellqty From V_XMK__SecInv_sell_Qty Where 1=1 "
If startdate <> " " Then
Sql_text = Sql_text & " And FDate>=" & " '" & startdate & " '"
End If
If enddate <> " " Then
Sql_text = Sql_text & " And FDate<=" & " '" & enddate & " '"
End If
Sql_text = Sql_text + " group by fitemid,fnumber,fname,flowlimit,fhighlimit,fqty Order by fnumber "
Sql_text = Sql_text + " union select *,0 As fsellqty from V_XMK_SecInvQty where fitemid not in (select fitemid"
Sql_text = Sql_text + " from V_XMK_V_XMK_SecInv_sell_Qty where 1=1 "
If startdate <> " " Then
Sql_text = Sql_text & " And FDate>=" & " '" & startdate & " '"
End If
If enddate <> " " Then
Sql_text = Sql_text & " And FDate<=" & " '" & enddate & " ')"
End If
rst.Open Sql_text, cn, adOpenStatic, adLockBatchOptimistic
Private Sub CommandButton1_Click()
Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim Sql_text,row1, startdate, enddate, Sql_text2, sqlstr As String
Dim i, j, k As Integer
startdate = Worksheets("sheet2").Cells(1, 5).Rows.Value
enddate = Worksheets("sheet2").Cells(1, 7).Rows.Value
cnnstr = "Provider = SQLOLEDB;" & _
"Data Source = 10.70.1.251;" & _
"Initial Catalog = AIS20120607184516;User ID =mro;Password =mro;"
cn.Open cnnstr
Sql_text = "Select fitemid,fnumber,fname,flowlimit,fhighlimit,fqty,sum(fsellqty) as fsellqty From V_XMK__SecInv_sell_Qty Where 1=1 "
If startdate <> " " Then
Sql_text = Sql_text & " And FDate>=" & " '" & startdate & " '"
End If
If enddate <> " " Then
Sql_text = Sql_text & " And FDate<=" & " '" & enddate & " '"
End If
Sql_text = Sql_text + " group by fitemid,fnumber,fname,flowlimit,fhighlimit,fqty Order by fnumber "
Sql_text = Sql_text + " union select *,0 As fsellqty from V_XMK_SecInvQty where fitemid not in (select fitemid"
Sql_text = Sql_text + " from V_XMK_V_XMK_SecInv_sell_Qty where 1=1 "
If startdate <> " " Then
Sql_text = Sql_text & " And FDate>=" & " '" & startdate & " '"
End If
If enddate <> " " Then
Sql_text = Sql_text & " And FDate<=" & " '" & enddate & " ')"
End If
rst.Open Sql_text, cn, adOpenStatic, adLockBatchOptimistic
#5
你在VBA里试一下,能赋值超过255个字符吗?
#6
没看清楚是VBA,刚刚打开excel试了一下
Private Sub CommandButton1_Click()
Dim s As String
Dim t As String, p As String
t = "[" & String(200, "t") & "]" & vbCrLf
p = "[" & String(300, "p") & "]" & vbCrLf
s = t & p
MsgBox s & vbCrLf & " len=" & Len(s)
End Sub
#7
应该是监视窗口只能显示255字符