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.

Start Microsoft Access and create a new blank database.

Create the following table:


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

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

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

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