Sort records in case sensitive order

Access sorts records in ascending or descending order without regard to case. However, by writing a few lines of Visual Basic for Applications (VBA) code, you can sort text by its ASCII character values. Sorting on the basis of the ASCII values differentiates the uppercase letters from the lowercase letters, and results in a case-sensitive order.

The following table demonstrates how an ascending sort order in Access differs from a case-sensitive sort order:

Pre-sort order	Ascending order	Case-sensitive order
c	a	A
D	A	B
a	B	C
d	b	D
B	c	a
C	C	b
A	D	c
b	d	d

Although the results in the Ascending order column might at first appear somewhat unpredictable, they are not. In the Ascending order column, "a" appears before "A" and " B" appears before "b." This occurs because, when evaluated as text values, "A" = "a" and "B" = "b," whether lowercase or uppercase. Access takes into account the original order of the values. In the Pre-sort order column, "a" precedes "A" and "B" precedes "b."

When the case-sensitive sort operation is performed, the text values are replaced with their ASCII values. For example, A = 65, a = 97, B = 66, b = 98, and so on.

Write the VBA code

1. Create a VBA module and type the following line in the Declarations section, if it is not already there:

   Option Explicit

2. Type the following procedure in a module in the Visual Basic Editor:

   Function StrToHex (S As Variant) As Variant
   '
   ' Converts a string to a series of hexadecimal digits.
   ' For example, StrToHex(Chr(9) & "A~") returns 09417E.
   '
      Dim Temp As String, I As Integer
         If VarType(S) <> 8 Then
            StrToHex = S
         Else
            Temp = ""
         For I = 1 To Len(S)
            Temp = Temp & Format(Hex(Asc(Mid(S, I, 1))), "00")
         Next I
            StrToHex = Temp
         End If
   End Function
   

   The preceding user-defined function, StrToHex, can be called from a query. When you pass the name of the sort field to this function, it will sort the field values in case-sensitive order.

3. Now, create a query from which you will call this function.

   On the Create tab, in the Queries group, click Query Design.

4. Select Add Tables (Show Table in Access 2013).

5. Drag the fields you want to the grid.

6. In the first blank column, in the Field row, type Expr1: StrToHex([SortField]).

   StrToHex is the user-defined function you created earlier. SortField is the name of the field that contains the case-sensitive values.

7. In the Sort cell, click Ascending or Descending.

   If you choose ascending order, value beginning with uppercase letters will appear before those that begin with lowercase letters. Applying a descending order sort does the opposite.

8. Switch to Datasheet view.

   Access displays the records, sorted in case-sensitive order.

Top of Page