Option Compare Database
Option Explicit
'http://bytes.com/topic/access/answers/211263-double-quotes-data
'by: Bruce Rusk
'Usage is as follows:
'
'strSQL = "SELECT * FROM tblPeople WHERE LastName Like " & SQLQuote
'(strString) & ";"
'
'you can also have it add wildcards to modify the search by adding additional
'boolean arguments:
'
'for a wildcard before the string (search for "*endofname"):
'
'strSQL = "SELECT * FROM tblPeople WHERE LastName Like " & SQLQuote
'(strString, True) & ";"
'
'for a wildcard after the string (search for "startofname*"):
'
'strSQL = "SELECT * FROM tblPeople WHERE LastName Like " & SQLQuote
'(strString, , True) & ";"
'--------------Begin Code---------------
Const SingleQuote = "'"
Const DoubleQuote = """"
Const StarSingleQuote As String = "*'"
Const SingleQuoteStar As String = "'*"
Const DoubleQuoteStar As String = """" & "*"
Const StarDoubleQuote As String = "*" & """"
Const CHRDouble As String = "' & CHR(34) & '"
Const CHRSingle As String = "' & CHR(39) & '"
Public Function SQLQuote(ByVal strIn As Variant, _
Optional ByVal PrefixWildCard As Boolean = False, _
Optional ByVal SuffixWildcard As Boolean = False _
) As String
Dim blnSingleQuote As Boolean
Dim blnDoubleQuote As Boolean
Dim blnCHRStart As Boolean
Dim blnCHREnd As Boolean
Dim strCompare As String
If IsDate(strIn) Then
SQLQuote = SQLDate(strIn)
Exit Function
End If
blnDoubleQuote = (InStr(strIn, DoubleQuote) <> 0)
If blnDoubleQuote Then
' There are double quotes; check for single
' quotes as well
blnSingleQuote = (InStr(strIn, SingleQuote) <> 0)
If blnSingleQuote Then
' Both single AND double quotes -- complicated,
' replace with CHR functions
' Replace single quotes with CHR version
strCompare = Replace$(strIn, SingleQuote, CHRSingle)
' Clean up front and back
If Left$(strCompare, 15) = CHRSingle Then _
strCompare = Mid$(strCompare, 5)
If Right$(strCompare, 15) = CHRSingle Then _
strCompare = Left$(strCompare, Len(strCompare) - 4)
' Replace double quotes with CHR version
strCompare = Replace$(strCompare, DoubleQuote, CHRDouble)
' Clean up front and back
If Left$(strCompare, 15) = CHRDouble Then _
strCompare = Mid$(strCompare, 5)
If Right$(strCompare, 15) = CHRDouble Then _
strCompare = Left$(strCompare, Len(strCompare) - 4)
' String parsed, now check for wildcards
' First, check whether the string starts or ends with
' a CHR(n) value
blnCHREnd = (StrComp(Right$(strCompare, 8), " CHR(34)")) = 0 Or _
(StrComp(Right$(strCompare, 8), " CHR(39)")) = 0
blnCHRStart = Left$(strCompare, 5) = "CHR(3"
' Add wildcard and start/end single quotes, as
' needed
'
If PrefixWildCard Then
If blnCHRStart Then
strCompare = "'*' & " & strCompare
Else
strCompare = SingleQuoteStar & strCompare
End If
ElseIf Not blnCHRStart Then
strCompare = SingleQuote & strCompare
End If
If SuffixWildcard Then
If blnCHREnd Then
strCompare = strCompare & " & '*'"
Else
strCompare = strCompare & StarSingleQuote
End If
ElseIf Not blnCHREnd Then
strCompare = strCompare & SingleQuote
End If
Else
' Only double quotes, so using single
' quotes around it is safe
If PrefixWildCard Then
strCompare = SingleQuoteStar & strIn
Else
strCompare = SingleQuote & strIn
End If
If SuffixWildcard Then
strCompare = strCompare & StarSingleQuote
Else
strCompare = strCompare & SingleQuote
End If
End If
Else
' No double quotes in the string
' so using double quotes around it
' is safe (even if there are single
' quotes in it).
'
If PrefixWildCard Then
strCompare = DoubleQuoteStar & strIn
Else
strCompare = DoubleQuote & strIn
End If
If SuffixWildcard Then
strCompare = strCompare & StarDoubleQuote
Else
strCompare = strCompare & DoubleQuote
End If
End If
SQLQuote = strCompare
End Function
'----------------End Code-------------------
Function SQLDate(varDate As Variant) As String
'Purpose: Return a delimited string in the date format used natively by JET SQL.
'Argument: A date/time value.
'Note: Returns just the date format if the argument has no time component,
' or a date/time format if it does.
'Author: Allen Browne. allen@allenbrowne.com, June 2006.
If IsDate(varDate) Then
varDate = varDate & ""
If DateValue(CDate(varDate)) = CDate(varDate) Then
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
Else
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
End If
End If
End Function
