SQL Search - Problems with apostrophe in the search string

[justshapes]

I am having problems with the apostrophe in the SQL search string. Using "O'Mally's Apples" as an example. Following is my current VBA code that is giving me the problem.

MySearch = Replace(Me.TextSearch, "'", "''")
MySearch = "*" & MySearch & "*"
LineThree = "WHERE ((([GLItems].Item) Like '" & MySearch & "'))" & vbNewLine
MyCriteria = LineOne & LineTwo & LineThree & LineFour

If I search for "O'Mally's" the query returns (blank)
If I search for "mally" it returns O'Mally's Apples

I can't just remove all of the apostrophe in the database.
I am at my limit.
Please HELP...

[OBP]

You could remove all the apostrophes in the database with VBA code, but then it would not be correct data.
There are a few of ways around this.
1. Copy the name field to a new Search field in the table and remove all the apostrophes in that field, which is used purely for searching and the original name field for email, reports etc.
2. Manipulate the Search string so that when apostrophes are present it only contains "mally"
3. In the replace statement for the search string you should be able to use Chr(39) which is an apostrophe with Chr(39) & chr(39)
I will look at the case of O'Mally's and test it.

[OBP]

This works when using a VBA SQL Recordset.

sstring = "O'Mally's"
sstring = Replace(sstring, Chr(39), Chr(39) & Chr(39))
MsgBox sstring
strSql = "SELECT tbl_allegation.* " & _
"FROM tbl_allegation " & _
"Where ReportLastName = '" & sstring & "'"

Set rs = CurrentDb.OpenRecordset(strSql)
If rs.RecordCount > 0 Then
rs.MoveLast
rs.MoveFirst
MsgBox rs.RecordCount & " - " & rs.ReportLastName
End If

The first msgbox is just there to show how O'Mally's ends up, the second to show that it has the correct record.

How are you using the SQL?

[justshapes]

You could remove all the apostrophes in the database with VBA code, but then it would not be correct data.
There are a few of ways around this.
1. Copy the name field to a new Search field in the table and remove all the apostrophes in that field, which is used purely for searching and the original name field for email, reports etc.
2. Manipulate the Search string so that when apostrophes are present it only contains "mally"
3. In the replace statement for the search string you should be able to use Chr(39) which is an apostrophe with Chr(39) & chr(39)
I will look at the case of O'Mally's and test it.

Your Option #1 seems like a $10.00 fix for a 10 cent problem but it is an option that I hadn't thought of. It would require duplicating a 50 character Item field in a lengthy file. The Item could reoccur several times in the database depending on how many suppliers there are for a particular Item and only a small portion of the Items contain an apostrophe.

I reviewed my original post and I had neglected to mention that my search in question is within a SubForm. It's too bad the field search works as advertised until the search contains an apostrophe.

At least I now have an option. Thanks and keep on providing the helpful input.

[SamT]

Using Quote Marks in Strings always throws me for a loop. So I use

Const Sq As String = "'"
or
Const Sq As String = Chr(39)
Const Dq As String = """

"O" & Sq & "Malley" & Sq & "s"

[OBP]

As well as Samt's option, you should be able to set the Subform's Recordsource to the SQL Recordset instead of setting the Form's Filter.
I haven't tried using the Form Filter with O'Mally's, I can take a look at it.

[OBP]

I have just checked the Allen Browne Search 2000 Database from 2006 and his Search Solution finds O'Mally's without any problem at all.
Enter O'Mally's in the table and see.
Here is a copy.