We help IT Professionals succeed at work.

How do I use & symbol as part of text in an Access query?

dwarfield2k2
dwarfield2k2 asked
on
342 Views
Last Modified: 2012-05-06
I'm trying to write a simple query where the & symbol is part of the name (say, D&D Enterprises or D&D Players).  I'm trying to find everything that starts with D&D, but no combination of quotes and wildcard symbols seems to work.  Any ideas?

Thanks...Dave
Comment
Watch Question

Do you want to select data that includes & with a wildcard or do you want to name a field with &?
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
What database? Works for me (Access) without problem. Have you tried

where yourcolumn like '%D&&D%'

Author

Commented:
I want to include data that includes the &.  The "&" is part of the name.
Try to use the char function

INSERT INTO TEST(TEXT) VALUES('D' || chr(38) || 'D')

Commented:
Another possibility is (again in SQL)
where charindex('&', yourcolumnname) <> 0
or another:

where patindex('%D&D%', yourcolumnname) <> 0  
btw, make sure your database collation is not case sensitive, or check for typos

Author

Commented:
Access drives me to distraction, because the wildcard operator that works for it, apparently isn't ANSI standard SQL.  I had to use * instead of %, so the expression that worked was like '*D&D*' I didn't use caps--usually I don't get lazy about case, but at the same time usually it doesn't make a difference with Access--until today!  Thanks.

Commented:
Next time make sure you clarify what database you are using, so we can post the solution that you are looking for. thanks.
Yep - though you want to do this in Oracle or somehting else, because of the wildcards - thats why I ask for the database. But keep in mind that you have to switch back to %, if you have an ODBC-table linked against an access-table, very confusing %-)

Author

Commented:
Okay...sorry  about that.  I should have been over in the Access Zone anyway.  I guess something's moving me away from Access and back to Oracle!
can only be gravity :-)
CERTIFIED EXPERT
Top Expert 2012

Commented:
>>Next time make sure you clarify what database you are using<<
It is in the title as in:
How do I use & symbol as part of text in an Access query?

Author

Commented:
The only reason I said "partially" was because of the SQL vs Access query wildcard issue.  Your suggestion gave me the spark I needed.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.