Build SQL String for field that contains an apostrophe 1

[theSizz]

Can someone please help me ?
I want to build a query using VBA.
I am using a form that has unbound text boxes to enter the criteria for the select query.
The user fills in the text boxes as required and then executes the code. The problem is one of the fields (Type) contains text data that occasionally has a name that contains an apostrophe.When the user enters a name such as Ted's into the text box on the form the code generates a syntax error as follows:
SYNTAX ERROR (MISSING OPERATOR) IN QUERY EXPRESSION Recap.Type = 'Ted's'

Here's the code:

Option Compare Database
Option Explicit

Private Sub cmdOK_Click()
' Pointer to error handler
    On Error GoTo cmdOK_Click_err
' Declare variables
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strStDt As String
    Dim strEndDt As String
    Dim strType As String
    Dim strDay As String
    Dim strSQL As String
' Identify the database and assign it to the variable
    Set db = CurrentDb

        Set qdf = db.QueryDefs("qryAutoQuery")
   
' Get the values from the combo boxes
    If IsNull(Me.txtStDt.Value) Then
        strStDt = " Like '*' "
    Else
        strStDt = ">=#" & Me.txtStDt.Value & "# "
    End If
    If IsNull(Me.txtEndDt.Value) Then
        strEndDt = " Like '*' "
    Else
        strEndDt = "<=#" & Me.txtEndDt.Value & "# "
    End If
    If IsNull(Me.cboType.Value) Then
        strType = " Like '*' "
    Else
        strType = "='" & Me.cboType.Value & "' "
    End If
    If IsNull(Me.cboDay.Value) Then
        strDay = " Like '*' "
    Else
        strDay = "='" & Me.cboDay.Value & "' "
    End If
' Build the SQL string
    strSQL = "SELECT recap.* " & _
             "FROM recap " & _
             "WHERE recap.dt" & strStDt & _
             "AND recap.dt" & strEndDt & _
             "AND recap.type" & strType & _
             "AND recap.dy" & strDay & _
             "ORDER BY recap.dt,recap.type;"
' Pass the SQL string to the query
    qdf.SQL = strSQL
' Turn off screen updating
    DoCmd.Echo False
' Check the state of the query and close it if it is open
    If Application.SysCmd(acSysCmdGetObjectState, acQuery, "qryAutoQuery") = acObjStateOpen Then
        DoCmd.Close acQuery, "qryAutoQuery"
    End If
' Open the query
    DoCmd.OpenQuery "qryAutoQuery"
cmdOK_Click_exit:
' Turn on screen updating
    DoCmd.Echo True
' Clear the object variables
    Set qdf = Nothing
    Set db = Nothing
    Exit Sub
cmdOK_Click_err:
' Handle errors
    MsgBox "An unexpected error has occurred." & _
        vbCrLf & "Please note of the following details:" & _
        vbCrLf & "Error Number: " & Err.Number & _
        vbCrLf & "Description: " & Err.Description _
        , vbCritical, "Error"
    Resume cmdOK_Click_exit
End Sub

I think the problem is with this piece:
strSQL = "SELECT recap.* " & _
"FROM recap " & _
"WHERE recap.dt" & strStDt & _
"AND recap.dt" & strEndDt & _
"AND recap.type" & strType & _
"AND recap.dy" & strDay & _
"ORDER BY recap.dt,recap.type;"

I just don't understand how to get the syntax right because sometimes the data in the Type field doesn't contain an apostrophe. By the way the code works fine if you enter a name like Johnson or Travis etc.

Can someone point me in the right direction?
Thanks in advance.

 

[genomon]

Need two single quotes - Replace(strType, "'", "''")

Outside of a dog, a book is man's best friend. Inside of a dog it's too dark to read.

 

[Remou]

You can use Replace:

[tt] Else
strType = "='" & Replace(Me.cboType.Value, "'", "''") & "' "[/tt]

 

[theSizz]

Thanks genomon and Remou.
Works perfect ! I really appreciate the quick response, you guys are great.

theSizz