Microsoft KB Archive/209632

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

Article ID: 209632

Article Last Modified on 6/24/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



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



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



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

</li></ol>

Additional query words: alpha numeric number text

Keywords: kbhowto kbinfo kbusage KB209632

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.