Sort records in case-sensitive order

Access sorts records in ascending or descending order without regard to case. However, you can write a few lines of Visual Basic for Applications (VBA) code to sort text by its ASCII character values. Sorting by ASCII values distinguishes uppercase letters from lowercase letters and gives you 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 seem unpredictable at first, they aren't. In that column, "a" appears before "A" and "B" appears before "b." This happens because, when Access evaluates them as text values, "A" = "a" and "B" = "b," whether they're lowercase or uppercase. Access then uses the original order of the values. In the Pre-sort order column, "a" comes before "A" and "B" comes before "b."

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

Write the VBA code

1. Create a VBA module. In the Declarations section, type the following line if it isn't 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 user-defined StrToHex function can be called from a query. When you pass the name of the sort field to this function, it sorts the field values in case-sensitive order.

3. Create a query that calls this function. On the Create tab, in the Queries group, click Query Design.

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

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 that you created earlier. Replace SortField with 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, values that begin with uppercase letters appear before values that begin with lowercase letters. Descending order does the opposite.

8. Switch to Datasheet View. Access displays the records, sorted in case-sensitive order.