Solved

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

Posted on 2009-05-07
14
323 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
0
Comment
Question by:dwarfield2k2
[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
  • 5
  • 4
  • 3
  • +1
14 Comments
 
LVL 7

Expert Comment

by:coffeeshop
ID: 24326045
Do you want to select data that includes & with a wildcard or do you want to name a field with &?
0
 
LVL 41

Accepted Solution

by:
ralmada earned 100 total points
ID: 24326143
select ... from yourtable
where yourcolumn like '%D&D%'
0
 
LVL 7

Expert Comment

by:coffeeshop
ID: 24326243
What database? Works for me (Access) without problem. Have you tried

where yourcolumn like '%D&&D%'
0
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 

Author Comment

by:dwarfield2k2
ID: 24326349
I want to include data that includes the &.  The "&" is part of the name.
0
 
LVL 7

Expert Comment

by:coffeeshop
ID: 24326417
Try to use the char function

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

Expert Comment

by:ralmada
ID: 24326429
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
0
 

Author Comment

by:dwarfield2k2
ID: 24326609
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.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 24326669
Next time make sure you clarify what database you are using, so we can post the solution that you are looking for. thanks.
0
 
LVL 7

Expert Comment

by:coffeeshop
ID: 24326754
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 %-)
0
 

Author Comment

by:dwarfield2k2
ID: 24326840
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!
0
 
LVL 7

Expert Comment

by:coffeeshop
ID: 24326917
can only be gravity :-)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24341598
>>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?
0
 

Author Closing Comment

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

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

733 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