Using SQL to replace an apostrophe

[Lauren H]

Hello,

I am using a SQL statement that fills a subform on a form with matching
records from a table where the criteria uses Like *criteria* functionality.

As an example, please refer to the following code:

Dim db As Database
Dim qdf As QueryDef
Dim strsql As String

Set db = CurrentDb

strsql = "SELECT [Person] FROM Tbl_Employees " & _
"WHERE [Person] Like '*' & '" & Me.Search & "' & '*'"

db.QueryDefs.Delete "tmpqry"
Set qdf = db.CreateQueryDef("tmpqry", strsql)

Me.search_results_subform.Form.RecordSource = strsql

---

This works fine for the most part, but I am running into problems with
searches with an apostrophe. I am aware of the replace function and have
tried replacing a single apostrophe with a double apostrophe (which works as
a good work-around in some cases, such as an INSERT INTO statement), but for
a search, it's actually searching for the double apostrophe and thus is not
returning any results.

Any direction that you can provide would be very much appreciated.

 

[Lauren H]

Thanks Marshall, the first option worked like a charm.