Single Quotes in Filters


Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

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

subs profile e1563205311409 - Single Quotes in Filters
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address