ACC: How to Sort String Values Based on their Numeric Values

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

This article describes a method that you can use to sort string values based on their numeric values.

MORE INFORMATION

The following example creates a query to sort alphanumeric strings by their numeric portion. There can be only one character digit after the number (that is, a, b, c; not aa, bc, cde). This query will work for any size numbers, with or without leading zeroes.

1. Start Microsoft Access and create a new blank database.

2. Create the following table:

         Table: Table1
         -----------------------
         Field Name: ProductCode
            Data Type: Text
   
   

3. Open the table in Datasheet view and enter the following records:

         10a
         1d
         100b
         24c
         24a
         1
         89b
         14
   

4. Create the following query based on Table1:

   NOTE: In the Field expression of the following query, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this query.

         Query: Query1
         ------------------------------------------------
         Field: ProductCode
            Show: Yes
         Field: PRE: Val([ProductCode])
            Sort: Ascending
            Show: No
         Field: SUF:IIf(Val(Right$([ProductCode],1))=0, _
                      Right$([ProductCode],1),"")
            Sort: Ascending
            Show: No
   
   

5. Run the query. the query separates the numeric value (PRE) and the string value (SUF) into two separate fields. The query sorts on the numeric portion of the ProductCode, and displays the complete ProductCode field as follows:

         1
         1d
         10a
         14
         24a
         24c
         89b
         100b