select * from tablename where custname like N'深圳' 在前面要加N
对了 首先对字符串进行分析 得到以空格分隔的共多少个子串 然后将各个子串拼成查询语句 Where field like '%SubStr1%' ... and like like '%SubStrn%'
恕我愚笨,如何得到子字符串的个数呢?我试过用楼上的查询语句,为什么查不到记录呢,我的代码如下,正确吗?请指教!(text1为输入查询条件的文本筐) Private Sub CmdSearch_Click() If Trim(Text1.Text) <> "" Then Dim strarray Dim rs As adodb.Recordset Set rs = New adodb.Recordset strarray = Split(Text1.Text, " ", -1, vbTextCompare) rs.Open "select * from TABLE where field like '%strarray(0)%' and field like '%strarray(1)%'", cn, adOpenKeyset, adLockReadOnly, adCmdText If rs.RecordCount <> 0 Then Set mshflexgrid.DataSource = rs end sub
首先你使用以上的代码查不到记录的原因是因为你在查询字符串中使用'%strarray(0)%'只会将数组名当作字符串输出,而非取指定数组变量中的值,你可以使用下列语句来查看一下(即如何在字符串中输出变量值): Dim sql as String sql="select * from TABLE where field like '%strarray(0)%' and field like '%strarray(1)%'" Print sql 结果你会发现此字符串是原样输出的,也就是说vb会把数组名当作字符串,而非按照你的意思去取数组变量中的值,你应该把它改为一下的形式才能按照你的想法在字符串中输出变量的值: rs.Open "select * from TABLE where field like '%" & strarray(0) & "%' and field like '%" & strarray(1) & "%', cn, adOpenKeyset, adLockReadOnly, adCmdText针对你提出的具体情况我把你的代码修改为以下形式:Private Sub CmdSearch_Click() If Trim(Text1.Text) <> "" Then Dim strarray Dim i As Long Dim sql As String Dim rs As adodb.Recordset Set rs = New adodb.Recordset strarray = Split(Text1.Text, " ", -1, vbTextCompare) sql = "select * from TABLE where" For i = LBound(strarray) To UBound(strarray) If i <> UBound(strarray) Then '判断是否是最后一个数组 sql = sql & "field like " & "'%" & strarray(i) & "%'" & " and " Else sql = sql & "field like " & "'%" & strarray(i) & "%'" End If Next rs.Open sql, cn, adOpenKeyset, adLockReadOnly, adCmdText If rs.RecordCount <> 0 Then Set mshflexgrid.DataSource = rs end sub现在应该可以了吧 see it?
SELECT * FROM [a] WHERE [a] LIKE '2002 中国' SELECT * FROM [a] WHERE [a]='2002 中国'试了试能找到啊~
strarray = Split(Text1.Text, " ", -1, vbTextCompare) if UBound(strarray)=0 then sql = "select * from TABLE " else sql = "select * from TABLE where" For i = LBound(strarray) To UBound(strarray) If i <> UBound(strarray) Then '判断是否是最后一个数组 sql = sql & "field like " & "'%" & strarray(i) & "%'" & " and " Else sql = sql & "field like " & "'%" & strarray(i) & "%'" End If Next end if
在前面要加N
得到以空格分隔的共多少个子串
然后将各个子串拼成查询语句
Where field
like '%SubStr1%'
...
and like like '%SubStrn%'
Private Sub CmdSearch_Click()
If Trim(Text1.Text) <> "" Then
Dim strarray
Dim rs As adodb.Recordset
Set rs = New adodb.Recordset
strarray = Split(Text1.Text, " ", -1, vbTextCompare)
rs.Open "select * from TABLE where field like '%strarray(0)%' and field like '%strarray(1)%'", cn, adOpenKeyset, adLockReadOnly, adCmdText
If rs.RecordCount <> 0 Then
Set mshflexgrid.DataSource = rs
end sub
Dim sql as String
sql="select * from TABLE where field like '%strarray(0)%' and field like '%strarray(1)%'"
Print sql
结果你会发现此字符串是原样输出的,也就是说vb会把数组名当作字符串,而非按照你的意思去取数组变量中的值,你应该把它改为一下的形式才能按照你的想法在字符串中输出变量的值:
rs.Open "select * from TABLE where field like '%" & strarray(0) & "%' and field like '%" & strarray(1) & "%', cn, adOpenKeyset, adLockReadOnly, adCmdText针对你提出的具体情况我把你的代码修改为以下形式:Private Sub CmdSearch_Click()
If Trim(Text1.Text) <> "" Then
Dim strarray
Dim i As Long
Dim sql As String
Dim rs As adodb.Recordset
Set rs = New adodb.Recordset
strarray = Split(Text1.Text, " ", -1, vbTextCompare)
sql = "select * from TABLE where"
For i = LBound(strarray) To UBound(strarray)
If i <> UBound(strarray) Then '判断是否是最后一个数组
sql = sql & "field like " & "'%" & strarray(i) & "%'" & " and "
Else
sql = sql & "field like " & "'%" & strarray(i) & "%'"
End If
Next
rs.Open sql, cn, adOpenKeyset, adLockReadOnly, adCmdText
If rs.RecordCount <> 0 Then
Set mshflexgrid.DataSource = rs
end sub现在应该可以了吧
see it?
SELECT * FROM [a] WHERE [a]='2002 中国'试了试能找到啊~
if UBound(strarray)=0 then
sql = "select * from TABLE "
else
sql = "select * from TABLE where"
For i = LBound(strarray) To UBound(strarray)
If i <> UBound(strarray) Then '判断是否是最后一个数组
sql = sql & "field like " & "'%" & strarray(i) & "%'" & " and "
Else
sql = sql & "field like " & "'%" & strarray(i) & "%'"
End If
Next
end if