ACC2000: How to Sort Alphanumeric Strings Based on Their Numeric Portions (209632)

MORE INFORMATION

The following example creates a query that sorts alphanumeric strings by their numeric portions. There can be only one alphabetical character after the numeric characters (such as "a" or "Z"; not "aa," "eee," or "BC"). This query works for any size numbers, with or without leading zeroes.

1. Start Microsoft Access, and then 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 then type 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 portion (PRE) and the alphabetical portion (SUF) into two separate fields. The query sorts on the numeric portion of the ProductCode field, and displays the complete ProductCode field as follows:

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