me.filter using 2 strings - problem with apostrophe.

[wackywoo105]

I’m using the following code to use one text box input to search for both first and surname. For say “Paul Davidson” I can type “da pa” or “d pau” etc.

The problem I have encountered is with apostrophes. If I type “o’c pa” for say “Paul O’Callaghan” it gives me a syntax error.

I have been looking into double-double quotes and char(34) but have been unable to implement them so that they work.

Can anyone help with this and get the code working for names with apostrophes?

Private Sub FilterON_Click()
    Dim nSpace As Integer, lastStr As String, firstStr As String
    
    Me.FilterON = False
    
    nSpace = InStrRev(Me![Filter], " ")
    
    If nSpace = 0 Then
        Me.Filter = "[surname] LIKE '" & Me![Filter] & "*'"
    Else
        lastStr = Trim(Mid(Me![Filter], 1, InStr(Me![Filter], " ")))
        firstStr = Trim(Mid(Me![Filter], InStr(Me![Filter], " ")))
        
        Debug.Print lastStr
        Debug.Print firstStr
        
        Me.Filter = "[Surname] LIKE '" & lastStr & "*' And [First Names] LIKE '" & firstStr & "*'"
    End If
    
    Me.FilterON = True
End Sub

[June7]

Was the solution to use Replace function to double the apostrophe?

[wackywoo105]

Started using char(34) but then switched to double-double quotes.

[June7]

What I do:

Replace([fieldname],"'", "''")