totn Access

MS Access 2003: View only records with uppercase letters for a certain field

This MSAccess tutorial explains how to view only records with uppercase characters in a certain field in Access 2003 (with screenshots and step-by-step instructions).

Question: In Microsoft Access 2003/XP/2000/97, if I wanted to view only the records with Uppercase letters for a certain field, how would I do it?

For example, the query would ignore "Bob", but show "JOHN".

Answer: Access was built to be case-INSENSITIVE, but we can always use the ASC function to determine upper vs lower case.

To query on case-sensitivity, we've created a function called ContainsLower. You will need to paste the following function into a Module in your Access database:

Function ContainsLower(pValue) As Boolean

   Dim LLength As Integer
   Dim LPos As Integer

   'Check for null value
   If IsNull(pValue) = False Then

      'Find length of string
      LLength = Len(pValue)

      LPos = 1

      While LPos <= LLength
         'Check if value is between 'a' to 'z'
         If Asc(Mid(pValue, LPos, 1)) >= 97 And Asc(Mid(pValue, LPos, 1)) <= 122 Then
            ContainsLower = True
            Exit Function
         End If

         LPos = LPos + 1
      Wend

   End If

   ContainsLower = False

End Function

This function will return FALSE if the value passed into the function contains all uppercase. It will return TRUE if at least one of the characters is NOT uppercase.

Next, you'll need to use this function in your query.

Microsoft Access

In the example above, we only want records where the FirstName field contains all uppercase. So we've typed "ContainsLower([FirstName])" in the field. Access assigns the field name of "Expr1" - you can always overwrite this.

Then we've typed FALSE in the criteria (we only want uppercase values returned).

Finally, we've unchecked the Show checkbox because we don't need to see what the ContainsLower function returns - we are only using this function in our "where clause".