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

View products that this article applies to.

This article was previously published under Q209632

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

This article applies only to a Microsoft Access database (.mdb).

Expand all | Collapse all

SUMMARY

This article shows you how to sort alphanumeric string values, based on their numeric portions.

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.

Start Microsoft Access, and then create a new blank database.

Create the following table:

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

Open the table in Datasheet view, and then type the following records:
10a
1d
100b
24c
24a
1
89b
14

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 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