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
Thanks...Dave
Do you want to select data that includes & with a wildcard or do you want to name a field with &?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What database? Works for me (Access) without problem. Have you tried
where yourcolumn like '%D&&D%'
where yourcolumn like '%D&&D%'
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')
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
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
ASKER
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 %-)
ASKER
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 :-)
>>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?
It is in the title as in:
How do I use & symbol as part of text in an Access query?
ASKER
The only reason I said "partially" was because of the SQL vs Access query wildcard issue. Your suggestion gave me the spark I needed.