Search field error with ' in name

[burnout]

I have a search form, generally it runs fine...until now. When the record contains a ' in it, I get an error. For example, I can search on last name Smith fine, but unable to search on O'Malley.

I get runtime error 3075
Syntax error (missing operator) in query expression 'lstnam LIKE 'O*M*' AND fstnam LIKE 'g*'

It works with other characters, like ? or . or !, just not the apostrophe '.

Any help would be great.

Here is the code I have for the search button.
Private Sub cmdSearch_Click()

Dim LSQL As String
Dim LSearchString As String
Dim LSearchString1 As String

If Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
MsgBox "Please enter a last name."
End If
If Len(txtSearchstring1) = 0 Or IsNull(txtSearchstring1) = True Then
MsgBox "Please enter a first name or portion of the first name"

Else

LSearchString = txtSearchString
LSearchString1 = txtSearchstring1

'Filter results based on search string
LSQL = "select * from tblTEMPMEM"
LSQL = LSQL & " where lstnam LIKE ' " & LSearchString & "*'" & " AND fstnam LIKE '" & LSearchString1 & "*'" & ""

Form_frmGroups_sub1.RecordSource = LSQL

lblTitle.Caption = "Member Details: Filtered by '" & LSearchString & " , " & LSearchString1 & "'"

'Clear search string
txtSearchString = ""
txtSearchstring1 = ""

MsgBox "Results have been filtered. All names containing " & LSearchString & "."



Thanks!

 

[burnout]

Any ideas?
Thanks in advance.

 

[RuralGuy]

LSQL = LSQL & " where lstnam LIKE " & Chr(34) & LSearchString & "*" & Chr(34) & " AND fstnam LIKE " & Chr(34) & LSearchString1 & "*" & Chr(34)

It will still be vulnerable to double quotes!

 

[burnout]

Thank you so much rural guy!

 

[RuralGuy]

Glad I could help.