Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Query criteria has an apostraphe!! what to do?

Status
Not open for further replies.

asenft

Programmer
Apr 24, 2001
52
US
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
 
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
 
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
 
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
 
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
 
if I understand you correctly just enclose in brackets

Like "*[']*" will bring up anything with an apostraphe
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top