Looking Up Strings With SQL

[matzos]

I need some help. I have forms that are performing a lookup based on the text selected in a list box. The SQL statement uses "...WHERE ClientName = " & "'" & lstClient.Text & "'"

This works for all occurences except if a ClientName has an apostrophe in the name itself...the SQL things that it is the end of the string and it gives me a run-time error because it also uses the apostrophe to mark the text. If I remove the surrounding apostrophes, the statement will not work at all.

I need to know how to lookup a string without using the surrounding apostrophes. I have tried several ideas but I am stuck.

I appreciate whetever help you can give! Thanks.

 

[genomon]

Use the replace function (common fix):
YourString = Replace(recordset.fieldname, "'", "''&quot
Where the first instance is a single quote surrounded by double quotes and the second instance is two single quotes surrounded by double quotes. Works like a charm for me!!
-Geno

 

[WilMead]

faq222-418 covers this too.

Wil Mead
wmead@optonline.net