How to search for string with ' (apostrophy) in it (special character)

[omniaccess]

I have a database that contains some names that have ' in them, such as O'Conner, etc. The search method I have in place comes back with an error because of the ' in the name when they search. The error says "Syntax error (missing operator) in query expression '[EntClientName] Like 'O'Conner'*''

The search string being put together in VB is:

Me.Filter = "[EntClientName] like '" & Me![ClientNameFindBox] & "*'"
Me.FilterOn = True

Where [EntClientName] contains both their first and last name merged together and [ClientNameFindBox] contains what they type in for their search string.

How do I need to modify this to take into account non-standard characters like ' in O'Connor?

I also have some other places that put together strings of "Starts with" where it adds the * at the end of the string, and "Contains" that adds a * at the front and end of the string. Everything works fine until a name with the ' in it, and then the error is the same.

If anyone has any insight please let me know.

Thank you

 

[traingamer]

As PHV pointed out in several previous posts, use the Replace function to double up the single quotes in the string:

...Replace(Me![ClientNameFindBox], "'", "''")...

That should get the single quote (apostrophe) through the SQL parser.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill