How to insert a reserved character in a variable?

  • Thread starter Thread starter Paul Mak
  • Start date Start date
P

Paul Mak

I have a vavriable called strSearch which I use it as part of a SQL
statement for a search, somehow it does not recongize the reserved character
like '. The coding to do the search as follows:

If Not rst1.BOF Then
rst1.MoveFirst
Do While Not rst1.EOF
strSearch = "'*" & rst1!Keyword & "*'"
'Append keywords search result to Tbl_ Temp
DoCmd.RunSQL "INSERT INTO Tbl_Temp ( ID, Country) " & _
"SELECT [Tbl_Data].ID, [Tbl_Data].Country " & _
"FROM [Tbl_Data] " & _
"WHERE ((([Tbl_Data].Title) Like " & strSearch & ") Or
(([Tbl_Data].Keywords)Like " & strSearch & ") Or (([Tbl_Data].Area)Like " &
strSearch & "));"
strSearch = vbNullString
rst1.MoveNext
Loop
End If
rst1.Close
Set rst1 = Nothing

It found all the records with the keywords from the rst1 except the
keywords like Jan's Doe or Microsoft's with a ' in it. Thanks.
 
I use Chr(34), the ASCII for a double-quote in cases like this. Using
double-quotes
will allow a single quote within the string.

Take out the single quotes.

strSearch = "*" & rst1!Keyword & "*"

Surround the string with Chr(34)

"WHERE ((([Tbl_Data].Title) Like " & Chr(34) & strSearch & Chr(34) & ") Or
....
 
Back
Top