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:

http://img.office-watch.com/waw/single%20quotes%20in%20filters.png image from Single Quotes in Filters at Office-Watch.com

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