Prev: Extract Data from a Field
Next: making SQL the source for a report?
From: Please Help Me on 14 Jan 2010 14:43
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
From: John Spencer on 14 Jan 2010 15:43
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

Please Help Me wrote:
> 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
 | 
Pages: 1
Prev: Extract Data from a Field
Next: making SQL the source for a report?