Solved

Filter in Access with "&"

Posted on 2013-11-11
14
480 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 
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

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

751 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