Quotes In String Causing SQL Error

[edwinbriggs]

Sorry, I couldn't find any post that has already answered this.

I have a huge project where at different occassions I am querying an access database. A part of the query is a string that comes from a text box and can sometimes contain single quotes.

For example:
str = "Hell's right here."
qry = "Select * From tblData Where fldComments = '" & str & "'"

This actaully results in qry being:
Select * From tblData Where fldComments = 'hell's right here'

Now SQL breaks it at the qoute after "hell" and makes the query:
Select * From tblData Where fldComments = 'hell'

And it doesn't know what to do with:
s right here'

and gives error.

One solution is to restrict the user from entering single quotes by setting the KeyAscii value to 0 at the KeyPress event of the text box.

There are two problems with this approach:
1. I have too many forms in the project and too many text boxes in each one of them to add KeyPress event handler for each. It will blow the file size besides killing me.
2. It will be rude to not let the user type it.

Can someone please suggest a gentler and more reasonable solution to this?

I am sure all you experts out there must have faced and dealt with this challenge.

Thanks a ton!

 

[edwinbriggs]

Forgot to mention this....

I cannot use Replace(str, "'", "") because during data entry when the user writes "Hell's right here.", I want to keep it as it is in the access database and return the same (without removing the quotes) back to the user when the string is searched for.

Thanks for your help.

 

[Andrew Poulsom]

Doubling up any single quotes should work:

qry = "Select * From tblData Where fldComments = '" & Replace(str, "'", "''") & "'"