|
做这种查询最重要的就是SQL语句的WHERE子句的设置,特别是单字段多关键字的时候,设置Where子句就比较难了。在此提供的一个函数可以很好的解决这个问题。相信对ASP中设计网络搜索引擎的熟悉的朋友,对下面的代码不会陌生,这个函数就是吸收了网络搜索引擎的代码而成的。关键字之间一般使用逗号、分号(不区分全角半角)或者空格来隔开,该函数首先将关键字中的分隔符:逗号、分号替换成空格,然后根据空格进行拆分,最后通过循环生产SQL语句的Where子句,同时根据需要使用AND或OR将他们连接在一起。使用下列函数也很简单:函数CSQL("表1","字段1","A,b;C",2)将返回的SQL语句为:”SELECT * FROM [表1] WHERE ([字段1] LIKE '*A*' OR [字段1] LIKE '*b*' OR [字段1] LIKE '*C*');”
将这句SQL语句赋给子窗体的RecordSource,然后用Requery更新子窗体,就可以实现单字段多关键字查询。
函数如下:
‘定义常量
ublic Const conAND As Integer = 1
ublic Const conOR As Integer = 2
'===============================================================================
'-函数名称: CSQL
'-功能描述: 将参数转换为模糊查询的SQL语句
'-输入参数说明: 参数1: 必选 strTable As String 表名称或者无参数的选择查询名称
' 参数2: 可选 strField As String 字段名称
' 参数3: 可选 strKeyWords As String 查询关键字
' 参数4: 可选 intOperation As Integer 运算符(常量:conAND或者1,conOR或者2)
'-返回参数说明:
'-使用语法示例: strSQL=CSQL("表1","字段1","A,b;C",conAND)
'-参考:
'-使用注意: 可使用中文的,;和英文的,;和空格来作为关键的分割符
'-兼容性: 2000,XP,2003
'-作者: fan0217@163.com
'-更新日期: 2006-06-20
'===============================================================================
ublic Function CSQL(strTable As String, _
Optional strField As String, _
Optional strKeyWords As String = "", _
Optional intOperation As Integer = 1) As String
On Error GoTo Err_CSQL
Dim strSQL As String, varKWArray As Variant
Dim intMax As Integer, i As Integer, strOperation As String
If intOperation <> conAND And intOperation <> conOR Then
MsgBox "intOperation参数设置不正确。", 16, "fan0217"
Exit Function
End If
If strKeyWords = "" Then
strSQL = "SELECT * FROM [" & strTable & "];"
Else
strKeyWords = Replace(strKeyWords, ";", " ") '将中文分号换成空格
strKeyWords = Replace(strKeyWords, ";", " ") '将英文分号换成空格
strKeyWords = Replace(strKeyWords, ",", " ") '将中文逗号换成空格
strKeyWords = Replace(strKeyWords, ",", " ") '将英文逗号换成空格
strKeyWords = Trim(strKeyWords) '去处首尾的空格
varKWArray = Split(strKeyWords) '将字符串根据空格拆开,获得一个数组
intMax = UBound(varKWArray) '获取数组的最大下标
If strKeyWords = "" Then
strSQL = "SELECT * FROM [" & strTable & "];"
Else
strSQL = "SELECT * FROM [" & strTable & "] WHERE "
If intMax = 0 Then
strSQL = strSQL & "[" & strField & "] LIKE '*" & varKWArray(0) & "*';"
Else
If intOperation = conAND Then
strOperation = " AND "
ElseIf intOperation = conOR Then
strOperation = " OR "
End If
For i = 0 To intMax
varKWArray(i) = Trim(varKWArray(i))
If i = 0 Then
strSQL = strSQL & "([" & strField & "] LIKE '*" & _
varKWArray(i) & "*'" & strOperation
ElseIf i = intMax Then
strSQL = strSQL & "[" & strField & "] LIKE '*" & _
varKWArray(i) & "*');"
Else
strSQL = strSQL & "[" & strField & "] LIKE '*" & _
|
本帖子中包含更多资源
您需要 登录 才可以下载或查看,没有帐号?注册
x
|