ACC: How to Sort Records in Case-Sensitive ( ASCII) Order

MORE INFORMATION

   Ascending   Case-Sensitive
    Order          Order
   --------------------------
        a              A
        A              B
        B              C
        b              D
        c              a
        C              b
        D              c
        d              d
				

1. Open an existing Microsoft Access database.
2. Create a new table with the following structure:

         Table: Sorting Test
         -------------------
         Field Name: Test
            Data Type: Text

3. View the Sorting Test table in Datasheet view, type the following eight records in the Test field, and then close the table:

   b
   d
   B
   A
   D
   a
   C
   c

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

   Option Explicit

5. Type the following procedure:

         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
   						

6. Create the following query based on the Sorting Test table:

         Query: CaseSensitive Sorting Test
         ---------------------------------
         Type: Select
         Field: Test
            Sort: not sorted
            Show: True
         Field: Expr1: StrToHex([Test])
            Sort: Ascending
            Show: False

7. Run the query. Note that the records are sorted in case-sensitive order. The uppercase characters (A-D) appear before the lowercase characters (a-d).