Filter in Access with "&"

I have a client that has upgraded from MSAccess 2007 to 2013. In 2007, when a text filter is applied on a field that has data with a "&", it would return results. Now in 2013, if a "&" is used, it returns no records. Is there a work around for this?
dgravittAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
mbizupConnect With a Mentor Commented:
Take a closer look at the last post by Dirk Goldar at the link posted by valli_an.  

When I tested this earlier, I was testing directly against a table, using the same features.  To mimic what you are doing, I've set up a form and am using the same filtering features.

Apparently the problem with the ampersand is not a new issue.

- Use the "Equals to" filter, not the Contains filter (which will give you the error you mentioned)
- Surround your text in double quotes    "J & J"
- If you want to find the text anywhere in the field rather that an exact match, use double quotes and asterisks --- again, with the Equals To filter, not the Contains filter:   "*J & J*"

These methods work for me, 100%, in Access  2007, 2010 and 2013
0
 
Valliappan ANSenior Tech ConsultantCommented:
You may try enclosing with [ ] (square brackets), like 'a[&]t' as mentioned in here:

http://support.microsoft.com/kb/826763

HTH.
0
 
mbizupCommented:
What is the syntax for the filter you are using?
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
dgravittAuthor Commented:
It is on a form where you right click on field and select Text Filters, Begins with and type J & J.
This is a company name field and many of the companies have a & in them.
0
 
dgravittAuthor Commented:
The brackets don't work. Thanks for the suggestion.
0
 
mbizupCommented:
Try doubling up on the &:

J && J

(The & is a special character - the operator for concatenation.)
0
 
dgravittAuthor Commented:
No, that doesn't work either.
0
 
dgravittAuthor Commented:
Like I said earlier, it worked fine in Access 2007
0
 
mbizupCommented:
I don't have Access 2013, so can't actually test this, but your original syntax works for me in 2010.

If doubling the &'s doesn't work, then double check your original data to verify that you are indeed entering it correctly... for example, check the exact spacing between letters and &s, and make sure that there is an exact match

ie: your data has a record with exactly J & J,  not just "J & J Enterprises" (which will not be flagged as a match.
0
 
dgravittAuthor Commented:
Yes, the data matches.
0
 
dgravittAuthor Commented:
That returns an error "Enter a valid value"
0
 
dgravittAuthor Commented:
Thanks for all the help.
0
 
Valliappan ANSenior Tech ConsultantCommented:
That was a good one from mbizup.

 Thanks dgravitt.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.