Apostrophe in where clause 1

[LonnieJohnson]

I have been programming in Access/VB for a while, but have just recently starting using a lot of SQL in my code. I have a situation where some fields in my records have an apostrophe, ie: Women's Communty Center

My SQL reads:

MyField.RowSource = "SELECT DISTINCT FIELD FROM TABLE " _
& "WHERE (ANOTHERFIELD) = '" & VARIABLE & "';"

THIS WORKS FINE FOR ANY RECORD THAT DOES NOT HAVE AN APOSTROPHE IN THE FIELD NAME.

THANKS. lcjohnson@kawvalley.org

 

[petermeachem]

If you replace
'" & VARIABLE & "';"

with " & chr$(34) & VARIABLE & & chr$(34) & ";"

it will work for '

chr$(34) is actually ", so it won't work for " in the string.

But I don't know anyone who has a " in his/her name.
Peter Meachem
peter@accuflight.com

 

[LonnieJohnson]

Thanks petermaechem,

This worked like a charm. Hope I'm able to return the favor someday.
lcjohnson@kawvalley.org

 

[petermeachem]

Cheers Peter Meachem
peter@accuflight.com