There shouldn't be an apostrophe in the field name. Your subject line says
the apostrophe is in the data. If that is the case, then yes, it can be
worked around. There are three common ways to work round this. 1) Double up
the double quotes instead of using apostrophes as delimiters for the string.
If you use the delimiter in the string, you have to use it two at a time to
let VBA know that you literally mean that you want the character instead of
intending to delimit the string. 2) Use Chr(34) to delimit the string. 3)
Replace the apostrophes in the data with 2 apostrophes for the search.
Examples:
Dim strMyString As String, strResult As String
strMyString = "O'Hare"
1a) strResult = "This is a test, " & strMyString
?strResult
This is a test, O'Hare
1b) strResult = "This is a test, """ & strMyString & """"
?strResult
This is a test, "O'Hare"
2) strResult = "This is a test, " & Chr(34) & strMyString & Chr(34)
?strResult
This is a test, "O'Hare"
3) strResult = "This is a test, '" & Replace(strMyString, " ' ", " ' ' ") &
"'"
'extra spaces in the Replace function for clarity.
?strResult
This is a test, 'O''Hare'
When the result in #3 is used in the search, the outside apostrophes will be
used as delimiters and the inside, double apostrophe will be treated as a
single apostrophe which is part of the data. Just as the doubled double
quotes were changed to single quotes in the result.
