selecting a name with Apostrophy creates error message

[Guest]

Hi,

I have a form with a combo box that is based on a query called
SelectCustomer. A user selects a customer and then clicks a command button.
Another form appears with customer information. This form is based on a
table. In the properties section, their is a filter that says [CustomerName]
= 'O'Sullivan, Jill'. This works fine for all names that do not have an
apostrophy but if there is one then I get an error that says "Syntax Error
(missing operator) in query expression '[CustomerName] = 'O'Sullivan, Jill".
Can someone help with how to eliminate problems with names that have
apostrophies in them.

Thanks,

 

[Douglas J Steele]

You need to double the internal apostrophe so that you have [CustomerName] =
'O''Sullivan, Jill', or else use double quotes as the delimiter
[CustomerName] = "O'Sullivan, Jill"

Assuming you're using Access 2000 or newer, you can use the Replace function
to double the internal apostophes:

Replace(NameField, "'", "''")

Exagerated for clarity, that's

Replace(NameField, " ' ", " ' ' ")