Quotes and Apostrophes in SQL statements (Access97)

Topic

New Reply

Hi All,

Searching posts turned up a series of posts dated March 9, 2001, and I still need some help.

StrMySearchCriteria = Trim(rst![MyListOfSearchCriteria])

StrMySearchCriteria could be one of the following:

Smith
O’Brian
TOYS ‘R’ US
TOYS “R” US

strSql1 = strSql1 & ” WHERE (((MyTblName.MyFieldName) = ‘” & strMySearchCriteria & “‘))”

How are quotes placed in the WHERE clause to handle the above criteria?

Thanks

Reply | Quote

Replies

One option is to replace any double quotes in the string with a pair of double quotes (using the VB Replace function), so (for example) Toys “R” Us becomes Toys “”R”” Us. Then enclose this string in the WHERE clause in double qoutes. Some loungers use chr(34) in their code to make it more readable.

So your code would look something like:

strSql1 = strSql1 & ” WHERE (((MyTblName.MyFieldName) = ” & chr(34) & Replace(strMySearchCriteria, chr(34), chr(34) & chr(34),1) & chr(34) & “))”

Since double quotes are being used as the enclosing quotes, single quotes appearing in the string don’t need any special attention.

Is there a more elegant way of doing this? Anyone? Anyone?

Hope this helps.

Reply | Quote

Tom I tried use the Replace function you suggested,
Replace(strMySearchCriteria, chr(34), chr(34) & chr(34),1)

I received the following error –

Undefined function ‘Replace’ in expression.

Is this function available in Access 97?

Reply | Quote

Office 97 VBA doesn’t have a built-in Replace function (Office 2000 and XP do have it). You can use this function instead:

Function ReplaceString(sIn As String, sWhat As String, sBy As String)
Dim intPos As Integer
Dim sResult As String
sResult = sIn
intPos = InStr(sResult, sWhat)
Do While intPos > 0
sResult = Left(sResult, intPos – 1) & sBy & Mid(sResult, intPos + Len(sWhat))
intPos = InStr(intPos + Len(sBy), sResult, sWhat)
Loop
ReplaceString = sResult
End Function

In the code provided by Tom, use

ReplaceString(strMySearchCriteria, Chr(34), Chr(34) & Chr(34))

Reply | Quote

Thanks Tom and Hans

Reply | Quote

What happens if sin is a Null string? Does this routine fail?
Pat

Reply | Quote

Hi Pat,

The ReplaceString function will return an empty string if sIn = vbNullString or sIn = “”. It will raise an error message if sIn is Null, because sIn is declared as a string. You can modify the function to return an empty string “” or Null if sIn is Null. I also inserted a check for sWhat = “”, because trying to replace “” leads to an infinite loop.

Function ReplaceString(sIn, sWhat As String, sBy As String)
Dim intPos As Integer
Dim sResult As String
If IsNull(sIn) Then
‘ The next instruction makes the function return Null if sIn is Null.
‘ If you want to return “” instead, omit this instruction.
ReplaceString = Null
Exit Function
End If
If sWhat = “” Then
ReplaceString = sIn
Exit Function
End If
sResult = sIn
intPos = InStr(sResult, sWhat)
Do While intPos > 0
sResult = Left(sResult, intPos – 1) & sBy & Mid(sResult, intPos + Len(sWhat))
intPos = InStr(intPos + Len(sBy), sResult, sWhat)
Loop
ReplaceString = sResult
End Function

Regards,
Hans

Reply | Quote

Hi Hans,
I was alluding to the fact that sin should be defined as a variant so as to capture the fact if it is null or not.
Thanks anyway,
Pat

Reply | Quote