Access 2010 SQL Search within a SubForm - Problems with apostrophe in the search string

Question

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

MySearch = Replace(Me.TextSearch, "'", "''")
MySearch = "*" & MySearch & "*"
LineOne = "SELECT xxxxxxxxxx & vbNewLine  
LineTwo = "From xxxxxxxxx & vbNewLine  
   The following line is where my problem is.
LineThree = "WHERE ((([GLItems].Item) Like '" & MySearch & "'))" & vbNewLine  
LineFour = "ORDER BY xxxxxxx;"
MyCriteria = LineOne & LineTwo & LineThree & LineFour
stDocName = "[GLItemsSubform]"
Me![GLItemsSubform].Form.RecordSource = MyCriteria

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 and the Double apostrophes don't work in this situation

I am at my limit.

Please HELP...

I assume you have tried searching for 'O''Mally''s' (Note they are two single quotes each time, not double quotes. (I don't use access but this is how standard SQL would handle it) - You probably need a % at the end to capture the Apples bit as well. — Alan, Sep 27 '17 at 00:11
I think you need to replace this line `MySearch = "*" & MySearch & "*"` from * to %: `MySearch = "%" & MySearch & "%"`. Check this article: [Using the Right Wildcard characters](https://msdn.microsoft.com/en-us/library/aa140104(office.10).aspx) — cha, Sep 27 '17 at 00:29
[How to debug dynamic SQL in VBA](http://stackoverflow.com/a/1099570/3820271) -- if that doesn't help directly, please add the output of `Debug.Print MyCriteria` to your question. — Andre, Sep 27 '17 at 07:05
I changed the line to MySearch = "%" & MySearch & "%" and to my surprise it works as advertised. Thanks so much I have been pulling my hair out on this one. I was researching in old Visual Basic books along with the on line digging and I could not find any thing like what you suggested. You taught a very old dog a new trick. Now I am going to read those articles that you noted. Thanks again — Mike, Sep 28 '17 at 01:53

score 1 · Answer 1 · answered Dec 16 '20 at 08:55

I had the same problem with Access 2019 and could resolve it by Trial&Error.

Following syntax led to Access runtime error '3075' (syntax error in search term):

…
Set BT = db.OpenRecordset("SELECT DISTINCT  …
SearchLine = Firstline(BT![FullAddress])
' FullAddress can comprise words such as " Queen's "
…
Set CT = db.OpenRecordset(" … WHERE [MyField] = '" & SearchLine & "' …
…

Thus I changed the apostrophes to quotes:

…
Set BT = db.OpenRecordset("SELECT DISTINCT  …
SearchLine = Firstline(BT![FullAddress])
' FullAddress can comprise words such as " Queen's "
…
… Set CT = db.OpenRecordset(" … WHERE [MyField] = """ & SearchLine & """
…

This works perfectly.

However, I would greatly appreciate if anybody could tell me a more generally useful syntax, which would work also when FullAddress comprises expressions such as: Ivan IV Vasilyevich "the terrible"'s Road 1530-84 !

score 0 · Answer 2 · answered Sep 27 '17 at 07:58

I am surprised it accomplishes anything - missing quote marks. Was that a typo error in post?

LineOne = "SELECT xxxxxxxxxx " & vbCrLf
LineTwo = "FROM xxxxxxxxx " & vbCrLf
LineThree = "WHERE GLItems.Item Like '" & MySearch & "' " & vbCrLf
LineFour = "ORDER BY xxxxxxxxx;"

Why bother with the vbCrLf? Why using xxxxxxxxx in posted question?

Tested the doubled apostrophe and works for me.

Access 2010 SQL Search within a SubForm - Problems with apostrophe in the search string

2 Answers2