I have a bit of code that parses the comments in a table to determine if the comment includes one of 32 preset words or phrases. I received an error because one of the words contained a single quote: Rec'd. So, I added the IF statement (InStr(1, rs5![ExcludeThisWord], "'") = 0) to skip that words with a single quote.
Code:
Do While Not rs5.EOF
If InStr(1, rs5![ExcludeThisWord], "'") = 0 Then
strExcludeThisWord = rs5![ExcludeThisWord]
strExcludeThese = " UPDATE tblComments SET tblComments.ExcludeMe = IIf(InStr(1,[comment],'" & strExcludeThisWord & "'),[ExcludeMe]+1,[ExcludeMe]) WHERE (((tblComments.AFS_NUMBER)>1)); "
db.Execute strExcludeThese, dbFailOnError
End If
rs5.MoveNext
Loop
Because the table with words and phrases is maintained by users I wanted to protect against other errors. When I added this to the IF statement ( InStr(1, rs5![ExcludeThisWord], '"') = 0) I get an error. This is using a double quote bracketed by single quotes. Both clauses work in the Access query designer but both do not work in VBA.
Is there a way to get this to work?
Are there other characters I need to be concerned about? I assume it is just the quotation marks because they are used by the command/function.