Ignore case in query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

What is the easiest way to ignore case in a query. Some data is entered in
uppercase. My query is comparing this with another table which is Title
Case. I am not getting results for a customer because it is case sensitive.
Thank you.
 
Queries are not case-specific and I could find no option to make them so.
Are you sure there is not some other criteria causing the item not to show
up? Have you tried entering your query in the same case to see if it will
then find the desired record? My guess is that it won't.
 
One way to do it is to upper all the field as well as you criteria.

Try this
select Field1, ........
from ........
WHERE UCase([Field1])=UCase(Criteria);
 
What is the easiest way to ignore case in a query. Some data is entered in
uppercase. My query is comparing this with another table which is Title
Case. I am not getting results for a customer because it is case sensitive.
Thank you.
Click to expand...

Access JET databases are NOT case sensitive, and cannot readily be
made so; is your data stored in some other database engine?

If need be you can use a criterion of

WHERE UCase([CustomerName]) = UCase([Enter customer name:])

but this will slow down searching drastically since it must call a
function twice for every row.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
If he is linked to an Excel Sheet then the ISAM does treat the data as
case-sensitive. (or it did in Access 97, I've not tested it recently).

I've not tested with links to text files, but the ISAM for that may also be case-sensitive.

John said:
What is the easiest way to ignore case in a query. Some data is entered in
uppercase. My query is comparing this with another table which is Title
Case. I am not getting results for a customer because it is case sensitive.
Thank you.
Click to expand...

Access JET databases are NOT case sensitive, and cannot readily be
made so; is your data stored in some other database engine?

If need be you can use a criterion of

WHERE UCase([CustomerName]) = UCase([Enter customer name:])

but this will slow down searching drastically since it must call a
function twice for every row.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
Back
Top