Search for a name with an apostrophe

[Please Help Me]

I have read as many threads as possible hoping to avoid having to ask this
question, as I know it is a frequent one, but I am having trouble with
apostrophes in SQL statements. Consider the following scenario, which is
more simple than reality but should summarize the issue.

I have a search form with one field/control called [Name]. A user will type
into a text box the name they are searching for and then click a Search
button. the search button then makes a table with the desired records so I
can then filter it and display the results on a subform

The issue is that an apostrophe often produces a syntax error. To fix this
I used the replace function, which is displayed below. This eliminated the
error but it does not return results with an apotrophe. For example....

say I wanted to search for "O'Neill"--after adding the replace function the
error is gone, but the record that has "O'Neill" does not show up in the
search results. This is what I have now, I have tried many different ways.

"SELECT * " & "INTO tblKeywordSearch " & "FROM MyTable" & " WHERE [Name]
Like '*" & Replace(Me.MyTextBox, "'", """") & "*'"

I do not post frequently so I am sorry if this is confusing, would be happy
to clarify, and appreciative of advice. Thanks

 

[John Spencer]

You need to replace the single quote with two single quotes

"SELECT * " & "INTO tblKeywordSearch " & "FROM MyTable" & " WHERE [Name]
Like '*" & Replace(Me.MyTextBox, "'", "''") & "*'"

That should result in a string the looks like
SELECT * INTO tblKeyWordSearch FROM MyTable Where [Name] Like '*O''Neill*'

The other alternative is to use two double quotes to get one double quote

"SELECT * " & "INTO tblKeywordSearch " & "FROM MyTable" & " WHERE [Name]
Like ""*" & Me.MyTextBox & "*"""

That should result in a string the looks like
SELECT * INTO tblKeyWordSearch FROM MyTable Where [Name] Like "*O'Neill*"


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County