SQL search with single quotes 1

[JBG]

Hi Access experts,

I need to be able to search my Access 2K db using a simple search engine written in VBA. The problem is using a statement like, for ex.,:

 strSQL = "SELECT strStoreName FROM tblStores WHERE strStoreName = '" & strSname & "'"

when strSname contains, for example: "Joe's Garage."

Obviously, the "'" fubars the strSQL string. I understand that I can go to ADO and use the find method to get around this, or, translate all of the existing data in my large db and get rid of all of the "'"s.

What I want to know is if anyone has a different method of getting around this issue? I'd like to be able to somehow translate the search criteria (i.e., what is contained in strSname) if it has a "'" in it, then run the search as is, and somehow get the search criteria to match existing criteria in the db that could have a "'".

I hope I have made sense. If so, and if you know a work-around, please reply.

Thanks!

JBG

 

[930driver]

You'll have to surround your strName with double quotes instead of single quotes. That way your single quote will be ignored. This can be a bit confusing the first time you see it.

To embed a double quote within a string you have to double it up like this "" so that the first of the two isn't considered a close quote. So the end of your sql would look like this:

WHERE strStoreName = """ & strSname & """"

In the string of three quotes the first two are the double quote and the third is the close quote. The open quote is at the very beginning of your sql string.

In the string of four quotes the first is the open quote, the next two are the double quote, and the fourth is the close quote.

Remember that the double quote will be interpreted by VBA as a single (double) quote when it runs.



"The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[JBG]

Thanks!