在VB程序中格式化SQL字符串

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