Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.

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

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×