Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. ACC2000: How to Sort Alphanumeric Strings Based on Their Numeric Portions

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

View products that this article applies to.

Summary

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

↑ Back to the top

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

↑ Back to the top

Keywords: KB209632, kbusage, kbinfo, kbhowto

↑ Back to the top

Article Info
Article ID : 209632
Revision : 2
Created on : 6/24/2004
Published on : 6/24/2004
Exists online : False
Views : 270