Link to home
Start Free TrialLog in
Avatar of dwarfield2k2
dwarfield2k2

asked on

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

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
Avatar of coffeeshop
coffeeshop
Flag of Germany image

Do you want to select data that includes & with a wildcard or do you want to name a field with &?
ASKER CERTIFIED SOLUTION
Avatar of ralmada
ralmada
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
What database? Works for me (Access) without problem. Have you tried

where yourcolumn like '%D&&D%'
Avatar of dwarfield2k2
dwarfield2k2

ASKER

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')
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
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.
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 %-)
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 :-)
Avatar of Anthony Perkins
>>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?
The only reason I said "partially" was because of the SQL vs Access query wildcard issue.  Your suggestion gave me the spark I needed.