Ignore case in query

[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.

 

[Rick B]

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.

 

[Guest]

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);

 

[John Vinson]

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.

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

 

[John Spencer (MVP)]

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.

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.

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

 

[John Vinson]

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

That it is... I got burned good by that a couple of years ago! Thanks
John, I'd forgotten that possibility.

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