Names with a ' in queries (or SQL)

[Real Wally]

G'Day all,

I'm having trouble with a query (or maybe with the SQL) that refuses to accept names that have an ' in them like e.g. M'R or van 't XX
If I look at the qry results the names with the ' are found. However, I have a button on the form that opens a subsequent form linked by the sirname. That works great for every name except for ones with the ' in them. I get the message that there's a syntax error in the query expression. If I take the ' out I have no problem but I'd have a misspelled name. What's the problem with the ' and is there a way around it?

Thanks,

Wally

 

[namliam]

You are probably pasing the sirname to the second form using something like:

"Sirname = '" & me.sirname & "'"

which translates to Sirname = 'van 't Hek'
Where you can see your problem: 3x'
solution:
"Sirname = " & Chr(34) & me.sirname & Chr(34)
OR
"Sirname = """ & me.sirname & """"
which translates to Sirname = "van 't Hek"

However now you WILL have the same troubles as before when you have van "t Hek ... But that rarely happens...

Regards

 

[Real Wally]

Got it!!

Thanks again Namliam, problem solved again.

Wally