CODEWITHSUNDEEP
sqlms-accesssearchvba
Mike
Mike

Reputation: 1

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

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...

Upvotes: 0

Views: 95

Answers (2)

Bughater
Bughater

Reputation: 73

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 !

Upvotes: 1

June7
June7

Reputation: 21370

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.

Upvotes: 0

Related Questions