Double Quotes In Data?

[(PeteCresswell)]

Is his just a flat-out "No-No" or is there some workaround when it comes time
for SQL searches and DAO.FindFirsts against fields containing same?

I can see maybe wrapping the value searched for in single quotes to deal with
embedded double quotes, but if there are both embedded single AND double quotes,
I can't see any way to deal with it.

So, am I doing the right thing to remove all embedded double quotes from my data
and write validation routines to enforce same in the data entry screens?

Or is there some workaround?
--
PeteCresswell

[Gord]

Try replacing each double-quote with a pair of double-quotes, e.g. ...

Edwin "Buzz" Aldrin

.... becomes ...

"Edwin ""Buzz"" Aldrin"

It has always worked for me.

[Bruce Rusk]

The most thorough way is to wrap the string in a function that checks for
quote marks and adjusts accordingly.

Here's a function I use to wrap string that I'm passing to an SQL string
that will be used in a query to ensure that the quote marks are handled
properly. It's based on some code David Epsom posted here several years ago.
Sorry for the dearth of comments in the code.

The function looks inside the string for single or double quotes. If there's
only one kind or the other, it encloses it in the other kind (e.g. a string
with an apostrophe gets double quotes around it, a string with double quotes
gets single quotes around it). If there are *both* (imagine if Edwin "Buzz"
Aldrin were Edwin "Buzz" O'Hare), it replaces the quotes with the equivalent
CHR() functions.

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 String, _
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

blnDoubleQuote = InStr(strIn, DoubleQuote)

If blnDoubleQuote Then
' There are double quotes; check for single
' quotes as well

blnSingleQuote = InStr(strIn, SingleQuote)

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

[Gord]

Hi, Bruce.

If there are *both* (imagine if Edwin "Buzz" Aldrin were Edwin "Buzz"
O'Hare), it replaces the quotes with the equivalent CHR() functions.

There's nothing wrong with doing that, but

"Edwin ""Buzz"" O'Hare"

is a valid string as-is.

[Darryl Kerkeslager]

Public Function S2SQL(ByVal inString As String) As String
Dim s As String
Dim i As Long
Do
i = InStr(inString, "'") ' double, single, double
If i = 0 Then Exit Do
s = s & Left$(inString, i - 1) & "''" ' d, s, s, d
inString = Mid$(inString, i + 1)
Loop
S2SQL = "'" & s & inString & "'" ' d, s, d - twice
End Function
--
Darryl Kerkeslager