|
Option Compare Database
'-----------------------------------------------------
'類模塊名 ：clsWhere
'建立方法 ：VBE 界面 -> 菜單 -> 插入 -> 類模塊
'作用 ：根據(jù)界面輸入，動態(tài)組織 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 函數(shù)專門用于組合常用的多條件搜索的Where子句
'參數(shù)說明：
' strFieldName ：用于傳入需要查詢的字段名
' varValue ：用于傳入窗體上對應控件的值，可能是 NULL
' strValueType ：可選參數(shù)，用于指定數(shù)據(jù)類型，默認為 string
' intOperator ：可選參數(shù)，用于指定操作符類型，默認為 like
' strAlertName ：可選參數(shù)，如果有錯誤，提示用戶是哪個項目出錯了，默認為 ""
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) & "”不是正確的數(shù)值，請再次復核！" & 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 執(zhí)行一遍來檢測 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
|
