Sort records in case-sensitive order

Microsoft Office Access 2007 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 orde r

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

  1. 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.

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

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

  3. In the Show Table dialog box, click the table that you want to sort, then click Add.

  4. Drag the fields you want to the grid.

  5. 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.

  6. 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.

  7. Switch to Datasheet view.

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

Top of Page

Applies To: Access 2007



Was this information helpful?

Yes No

How can we improve it?

255 characters remaining

To protect your privacy, please do not include contact information in your feedback. Review our privacy policy.

Thank you for your feedback!

Support resources

Change language