My VBA/SQL code searches customer comments (in a table) for keywords (from Keywords table) and loops through the keywords. That works perfectly for words without apostrophes (contractions like: can’t, don’t, didn’t, etc.) I now need to be able to search for contractions in addition to regular words like: slow, lost, terrible. Here’s my code that works great for regular words:

Private Sub cmdUpdateKW_Click()
Dim db As DAO.Database
Dim k As DAO.Recordset
Dim sql As String
Set db = CurrentDb
Set k = db.OpenRecordset("keywords")
    Do While Not k.EOF
        sql = "UPDATE Comments LEFT JOIN KWresults ON Comments.[Respondent ID] = KWresults.SMID " & _
            " SET KWresults.KW = '" & k!KW & "', KWresults.SMID = Comments.[Respondent ID], KWresults.L20comment = Left(Comments.qualified, 20) " & _
            " WHERE Comments.qualified LIKE '*" & k!KW & "*'"
    db.Execute sql
    k.MoveNext
Loop
MsgBox "KW Upload Complete", vbOKOnly
End Sub 

For each instance of K!KW I tried encasing KW with quotations and percent signs but no go. What is tricky, is the loop through keywords has both words without and words with apostrophe. So, I’m thinking I need to write the loop code twice in my procedure to account for words with and without apostrophe. But, there has to be a simpler SQL approach than running two instances of the loop code. Thanks for helping.