If you need to create a filter string for filtering by a person’s name, or a city name, you may run into this error:
This is because single quotes are used as the delimiter for Text strings, and there is a single quote in the city name. To get around this problem, make a calculated field like this:
TrimmedCity: Replace([City],Chr(39),””)
And then use this field when creating the filter string, like this:
strCity = Nz(Me.ActiveControl.Value)
strCity = Replace(strCity, Chr(39), “”)
strCityFilter = “[TrimmedCity] = ” & Chr(39) & strCity & Chr(39)
This will filter without a problem, while you can still display the city name (or a person’s name like O’Hara) on forms and reports