Question

请高手指点：SQL查询数值型问题，用的是foxpro数据库，金额设置的类型为字符型.

Private Sub Command1_Click()
Dim SQL As String
Dim ConnStr As String
Dim Conn As ADODB.Connection
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Set Conn = New ADODB.Connection

'On Error GoTo MyErr:
ConnStr = "Provider=MSDASQL.1;Driver=Microsoft Visual Foxpro Driver;SourceDB=" & App.Path & "\data;Mode=ReadWrite;SourceType=DBF;;Persist Security Info=False"
Conn.Open ConnStr
rs.CursorLocation = adUseClient
SQL = "SELECT * FROM mingxi ORDER BY no"
If Val(Text2.Text) > 0 Then
If Text1.Text = "" Then
SQL = SQL & "and val(金额) <= & Val(Text2.Text) & "
Else
SQL = SQL & "and 金额 between & Val(Text1.Text) & and " & Val(Text2.Text) & ""
End If
End If
rs.Open Trim$(SQL), Conn, adOpenDynamic, adLockOptimistic
If rs.RecordCount > 0 Then
Text3.Text = rs.Fields("日期")
Text4.Text = Format(rs.Fields("金额"), "0.00")
Else
MsgBox "查无数据!", 64, "提示"
Exit Sub
End If
rs.Close
'exit sub
'MyErr:
'Set rs = Nothing
'Set Conn = Nothing '释放相关的系统资源
'MsgBox Err.Description, vbInformation, "系统提示" '显示出错信息
End Sub
错误提示：commands contains unrecognized phrase/keyword.

Answer 1

按执行顺序，生成的SQL语句显然不正确
SQL = "SELECT * FROM mingxi ORDER BY no"
& "and val(金额) <= & Val(Text2.Text) &“
应该是Select * From mingXi Where ... and val(金额) <= & Val(Text2.Text) &“

Answer 2

Private Sub Command1_Click()
Dim SQL As String
Dim ConnStr As String
Dim Conn As ADODB.Connection
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Set Conn = New ADODB.Connection

'On Error GoTo MyErr:
ConnStr = "Provider=MSDASQL.1;Driver=Microsoft Visual Foxpro Driver;SourceDB=" & App.Path & "\data;Mode=ReadWrite;SourceType=DBF;;Persist Security Info=False"
Conn.Open ConnStr
rs.CursorLocation = adUseClient
SQL = "SELECT * FROM mingxi ORDER BY no"
If Val(Text2.Text) > 0 Then
If Text1.Text = "" Then
SQL = SQL & "and val(金额) <= " & Text2.Text
Else
SQL = SQL & "and 金额 between " & Text1.Text & " and " & Text2.Text
End If
End If
rs.Open Trim$(SQL), Conn, adOpenDynamic, adLockOptimistic
If rs.RecordCount > 0 Then
Text3.Text = rs.Fields("日期")
Text4.Text = Format(rs.Fields("金额"), "0.00")
Else
MsgBox "查无数据!", 64, "提示"
Exit Sub
End If
rs.Close
'exit sub
'MyErr:
'Set rs = Nothing
'Set Conn = Nothing '释放相关的系统资源
'MsgBox Err.Description, vbInformation, "系统提示" '显示出错信息
End Sub

Answer 3

做任务