Microsoft KB Archive/96112

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

Article ID: 96112

Article Last Modified on 1/18/2007

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q96112



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



SUMMARY
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:      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),&quot;&quot;) 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: <pre class="fixed_text">     1 1d 10a 14     24a 24c 89b 100b </li></ol>

Keywords: kbhowto kbusage KB96112

-

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

© Microsoft Corporation. All rights reserved.