Single Quotes in Filters
Office for Mere Mortals helps people around the world get more from Word, Excel, PowerPoint and Outlook. Delivered once a week. free.
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:
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
Office Watch has the latest news and tips about Microsoft Office. Independent since 1996. Delivered once a week.