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

ID: Q96112


The information in this article applies to:
  • Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97


SUMMARY

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

This article shows you how to sort alphanumeric 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 


Keywords : kbusage QryOthr
Version : 1.0 1.1 2.0 7.0 97
Platform : WINDOWS
Issue type : kbhowto


Last Reviewed: March 20, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.