Query criteria has an apostraphe!! what to do?

[asenft]

How can i run a query with criteria that has an apostraphe in it? I cant change the data cause it is linked to other tables by this field. Is it possible?? Any ideas of how to get around this issue? Thanks.

Aaron

 

[jebry]

Hi Aaron!

Set up a global constant in a module:

Public Const Quote = """"

Then use it like this:

Select * From YourTable Where YourField = " & Quote & YourCriteria & Quote

hth Jeff Bridgham
bridgham@purdue.edu

 

[rbowes]

Aaron, if you're using Access 2000 you can use the Replace() function. What i do is check my criteria strings for single quotes then replace it with chr(44) which is an apostrophe (single quote is chr(39)) ex:


strCriteria = Replace(strCriteria, "'", chr(44))

that works just great. Check the object browser to get all of the functionality of Replace() and look in Help for Character Sets


Hope that helps.

Bob

 

[asenft]

I tried both of your suggestions and neither one worked for my situation..

Jeff- I still got a missing operator error cause of the apostrophe ..

Bob- It doesnt find any results for the query because it changes the text string when i do a replace.

Any other ideas?

-Aaron

 

[jebry]

Hi Aaron!

Quick question: Are you running a parameter query with the criteria in a combo box on a form? If not, where is the data coming from?

Jeff Bridgham
bridgham@purdue.edu

 

[braindead2]

if I understand you correctly just enclose in brackets

Like "*[']*" will bring up anything with an apostraphe

 

[asenft]

Jeff-
Yes, the criteria is coming from a combo box on a form. I actually got it figured out using the replace command. I replaced the single quote (apostraphe) with 2 single quotes and it worked.

-Aaron