Printable View
I need to find a record in an Access db from VB containing the string like this: "A Beginner's Guide to Basic". The code:
strCriteria = "Title LIKE '" & "A Beginner's Guide to Basic" & "'"
adoPrimaryRS.Find strCriteria
failes because of single quote character. Replacing a single quote with *, ? , CHR(39) doesn't work either. Incerting \ and \\ before ' (advice from C++ programmer) doesn't work too. Any ideas how to work with such a kind of data?
Thank you.
Vlad
strCriteria = "Title LIKE " & """A Beginner's Guide to Basic"""
adoPrimaryRS.Find strCriteria
Did you try it?
The ANSI character code for a single quotation mark is 39. Try using Chr(39) in your code intead of the single quotes.
E.g. Chr(39) & "An intro to BASIC" & Chr(39)
The reason it code is failing is that SQL sees your single quote as a delimiter for the end of string and tries to parse the rest of the string as SQL code. To fix this you need to do a replace operation on your string to change all instances of a single quote to two single quotes (not a double quote). This will tell SQL that a single quote is a part of the string and not the end of it.
This should be done on ALL strings in ANY SQL statment because you have no idea what the user will try.
I said about this in an original posting. Doesn't work.
Vlad
If I replace a single quote with 2 single quotes, it works, but only if the string contained 1 single quote. If more, still doesn't work. So I can find the string "Beginner's Guide" using "Beginner''s Guide" , but I'm getting an error if the string is "O'Henry's Book" and I use either "O''Henry''s Book" or "O''Henry's Book".
Any help, please
Vlad
If you use VB6, use the Replace$ function eg.
sSQL = "SELECT * FROM table WHERE textfield = '" & Replace$(SearchString, "'", "''") & "'"
this has always worked for me
Crazy D @ Work :-)