|
Option Compare Database
'-----------------------------------------------------
'类模块名 ：clsWhere
'建立方法 ：VBE 界面 -> 菜单 -> 插入 -> 类模块
'作用 ：根据界面输入，动态组织 SQL 语句的 Where 子句
'作者 ：cg1
'-----------------------------------------------------
'先定义几个枚举常量
Public Enum ValueTypeEnum
vDate = 1
vString = 2
vNumber = 3
End Enum
Public Enum OperatorEnum
vLessThan = 0
vMorethan = 1
vEqual = 2
vLike = 3
End Enum
Private strSQLWhere As String
Private strErrorDescription As String
Public Property Get ErrorDescription() As String
ErrorDescription = strErrorDescription
End Property
Public Property Get WhereWords() As String
'用于判断最终结果是否有 WHERE 子句，因为有可能是不需要条件，查询出所有的结果集合
Dim strOutput As String
If strErrorDescription <> "" Then
Debug.Print strErrorDescription
WhereWords = ""
Exit Property
End If
If IsNull(strOutput) = True Then
WhereWords = ""
Exit Property
Else
strOutput = strSQLWhere
End If
If strOutput <> "" Then
strOutput = " where " & strOutput
End If
If Right(strOutput, 5) = " and " Then
strOutput = Mid(strOutput, 1, Len(strOutput) - 5)
End If
WhereWords = strOutput
End Property
Public Function JoinWhere(ByVal strFieldName As String, _
ByVal varValue As Variant, _
Optional ByVal strValueType As ValueTypeEnum = 2, _
Optional ByVal intOperator As OperatorEnum = 3, _
Optional ByVal strAlertName As String = "")
'出处 ：http://access911.net
'作者 ：cg1
'说明：
'JoinWhere 函数专门用于组合常用的多条件搜索的Where子句
'参数说明：
' strFieldName ：用于传入需要查询的字段名
' varValue ：用于传入窗体上对应控件的值，可能是 NULL
' strValueType ：可选参数，用于指定数据类型，默认为 string
' intOperator ：可选参数，用于指定操作符类型，默认为 like
' strAlertName ：可选参数，如果有错误，提示用户是哪个项目出错了，默认为 ""
Dim strOperateor As String
Select Case intOperator
Case 0
strOperator = " <= "
Case 1
strOperator = " >= "
Case 2
strOperator = " = "
Case 3
strOperator = " Like "
Case Else
strOperator = " Like "
End Select
Select Case strValueType
Case 1 'date
If IsNull(varValue) = False Then
If IsDate(varValue) = True Then
JoinWhere = " (" & strFieldName & strOperator & " #" & CheckSQLWords(CStr(varValue)) & "#) and "
Else
strErrorDescription = strErrorDescription & "您" & IIf(strAlertName = "", "", "在“" & strAlertName & "”中") & "填写的“" & CStr(varValue) & "”不是有效的日期，请再次复核！" & vbCrLf
End If
End If
Case 2 'string
If IsNull(varValue) = False Then
JoinWhere = " (" & strFieldName & strOperator & " '*" & CheckSQLWords(CStr(varValue)) & "*') and "
End If
Case 3 'number
If IsNull(varValue) = False Then
If IsNumeric(varValue) Then
JoinWhere = " (" & strFieldName & strOperator & CheckSQLWords(CStr(varValue)) & ") and "
Else
strErrorDescription = strErrorDescription & "您" & IIf(strAlertName = "", "", "在“" & strAlertName & "”中") & "填写的“" & CStr(varValue) & "”不是正确的数值，请再次复核！" & vbCrLf
End If
End If
Case Else
JoinWhere = ""
End Select
strSQLWhere = strSQLWhere & JoinWhere
End Function
Private Function CheckSQLWords(ByVal strSQL As String) As String
'检查 SQL 字符串中是否包含非法字符
If IsNull(strSQL) Then
CheckSQLWords = ""
Exit Function
End If
CheckSQLWords = Replace(strSQL, "'", "''")
End Function
Public Function CheckSQLRight(ByVal strSQL As String) As Boolean
'用 EXECUTE 执行一遍来检测 SQL 是否有错误，只适用于耗时较少的 SELECT 查询
On Error Resume Next
CurrentProject.Connection.Execute strSQL
If Err <> 0 Then
Debug.Print Err.Number & " -> " & Err.Description
CheckSQLRight = False
Exit Function
End If
CheckSQLRight = True
End Function
|
