在寫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