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

fanpingli

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

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