Single Quotes in Filters

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: image from Single Quotes in Filters at

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

Want More?

Office Watch has the latest news and tips about Microsoft Office. Independent since 1996. Delivered once a week.