在寫SQL語句時,需要對不同類型的數據分別加上#號,""號等來表示,用以下函數,就可以實現操作的簡化.不管是什麼類型,只需用這個Q函數轉化一下,不需動手加格式化符號,就OK了.實在是方便.本人一直在用它,實在是方便.
Function Q(ByVal SqlVariable As Variant) As String
'-----------------------------------------
' Notes: Useful in creating properly formatted SQL statements
' Usage: sql="select * from table where name= " & Q(vntName)
' 這個版本格式化適用於Access的變量，若支持其它數據庫或許需要對其進行修改
'-----------------------------------------
On Error GoTo ErrTrap
Q = SqlVariable
'format the string
Select Case VarType(SqlVariable)
Case vbNull, vbEmpty
Q = "NULL"
Case vbString
Q = "'" & Replace(SqlVariable, "'", "''") & "'"
'date variable
Case vbDate
'format and enclose in pounds signs for Access
Q = "#" & Format$(SqlVariable, "general date") & "#"
'otherwise treat as numeric
Case Else
On Error Resume Next
Q = CStr(SqlVariable)
If Err.Number <> 0 Then Q = SqlVariable
End Select
Exit Function
ErrTrap:
On Error GoTo 0
End Function
