Share this page:

MS Access 2007: Create a query to differentiate between upper and lower case

This MSAccess tutorial explains how to create a query to differentiate between uppercase and lowercase in Access 2007 (with screenshots and step-by-step instructions).

Question: In Microsoft Access 2007, I have a table with a field that either has a 'P' (uppercase) value or a 'p' (lowercase) value. I need to write a query that can recognize the case difference. How can I do this?

Answer: To differentiate between case in Access, you can use the Asc function. The Asc function returns the NUMBER code that represents the specific character.

Let's go through an example. In ASCII, an uppercase 'P' would be 80, whereas a lowercase 'p' would be 112. To find out an ASCII value, you can use our ASCII table.

To help demonstrate our example, we've created a table called Products with a field called Type. In the Type field, you will either find the value 'P' or the value 'p'.

We've then created a query that uses the Asc function to check if the Type field has a value of 80 - which means that it is an uppercase 'P'.

Microsoft Access

When we run the query, we get the following results:

Microsoft Access

Now, if we wanted to find the records where the Type was lowercase 'p', we'd use the Asc function to check if the Type field has a value of 112.

Microsoft Access

Now, when we run the query, we get the following results:

Microsoft Access