(VB6)一个可以帮你实现多条件数据库搜索的函数

这个函数是我为了自己一款软件的开发需要而研究的，可以像搜索引擎那样输入多个搜索词进行搜索。比如我输入“张三 李四”，就可以在数据库中查找包含张三和李四的记录，而且这个函数调用很方便，下面就看代码吧。

 

Private Function MultiSQLSearch(TableName As String,SearchFields As String, SearchStr As String) As String
Dim itemSearchStr() As String  '搜索字符串
Dim itemSearchFields() As String  '搜索字段
Dim SearchStrFormat As String  '区分搜索字符串
Dim SearchFieldsFormat As String  '区分搜索字段
Dim strSearch As String
Dim strSearchSQL As String
Dim i As Long
Dim m As Long
SearchStrFormat = " "
SearchFieldsFormat = ","
itemSearchStr = Split(SearchStr, SearchStrFormat)
If UBound(itemSearchStr) = -1 Then
    ReDim itemSearchStr(0)
    itemSearchStr(0) = ""
End If
'分开多个关键字搜索
For i = 0 To UBound(itemSearchStr)
   '生成SQL语句头
   strSearch = itemSearchStr(i)
   If i = 0 Then
   strSearchSQL = "select * from [" & TableName & "] where (instr("
   Else
   strSearchSQL = strSearchSQL & "and (instr("
   End If
   '分开字段搜索关键字
   itemSearchFields = Split(SearchFields, SearchFieldsFormat)
    For m = 0 To UBound(itemSearchFields)
         If m = 0 Then
             strSearchSQL = strSearchSQL & itemSearchFields(m) & ","""
         Else
             strSearchSQL = strSearchSQL & "or instr(" & itemSearchFields(m) & ","""
         End If
         strSearchSQL = strSearchSQL & Trim(strSearch) & """)"
    Next
    strSearchSQL = strSearchSQL & ")"
Next
MultiSQLSearch = strSearchSQL
End Function

 

 

调用只要使用类似 SQL=MultiSQLSearch("表1", "字段1,字段2,字段3", "张三 李四")即可。