Solved

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

Posted on 2009-05-07
14
320 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
  • 5
  • 4
  • 3
  • +1
14 Comments
 
LVL 7

Expert Comment

by:coffeeshop
Comment Utility
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
Comment Utility
select ... from yourtable
where yourcolumn like '%D&D%'
0
 
LVL 7

Expert Comment

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

where yourcolumn like '%D&&D%'
0
 

Author Comment

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

Expert Comment

by:coffeeshop
Comment Utility
Try to use the char function

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

Expert Comment

by:ralmada
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:dwarfield2k2
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
can only be gravity :-)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>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
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 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

16 Experts available now in Live!

Get 1:1 Help Now