Solved

Filter in Access with "&"

Posted on 2013-11-11
14
432 Views
Last Modified: 2013-11-14
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?
0
Comment
Question by:dgravitt
  • 7
  • 4
  • 3
14 Comments
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 39639576
You may try enclosing with [ ] (square brackets), like 'a[&]t' as mentioned in here:

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

HTH.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39639597
What is the syntax for the filter you are using?
0
 

Author Comment

by:dgravitt
ID: 39639608
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
 

Author Comment

by:dgravitt
ID: 39639614
The brackets don't work. Thanks for the suggestion.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39639624
Try doubling up on the &:

J && J

(The & is a special character - the operator for concatenation.)
0
 

Author Comment

by:dgravitt
ID: 39639633
No, that doesn't work either.
0
 

Author Comment

by:dgravitt
ID: 39639636
Like I said earlier, it worked fine in Access 2007
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 61

Expert Comment

by:mbizup
ID: 39639655
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
 

Author Comment

by:dgravitt
ID: 39639657
Yes, the data matches.
0
 
LVL 9

Assisted Solution

by:Valliappan AN
Valliappan AN earned 250 total points
ID: 39641240
0
 

Author Comment

by:dgravitt
ID: 39641597
That returns an error "Enter a valid value"
0
 
LVL 61

Accepted Solution

by:
mbizup earned 250 total points
ID: 39641716
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
 

Author Closing Comment

by:dgravitt
ID: 39645786
Thanks for all the help.
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 39647606
That was a good one from mbizup.

 Thanks dgravitt.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now