tech on the net

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

See solution in other versions of Access:

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