在寫SQL語句時(shí),需要對不同類型的數(shù)據(jù)分別加上#號,""號等來表示,用以下函數(shù),就可以實(shí)現(xiàn)操作的簡化.不管是什么類型,只需用這個(gè)Q函數(shù)轉(zhuǎn)化一下,不需動(dòng)手加格式化符號,就OK了.實(shí)在是方便.本人一直在用它,實(shí)在是方便.

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)

' 這個(gè)版本格式化適用于Access的變量，若支持其它數(shù)據(jù)庫或許需要對其進(jìn)行修改

'-----------------------------------------

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