ACC: How to Sort String Values Based on their Numeric Values
ID: Q96112
|
The information in this article applies to:
-
Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97
SUMMARY
Moderate: Requires basic macro, coding, and interoperability skills.
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),"")
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:
1
1d
10a
14
24a
24c
89b
100b
Keywords : kbusage QryOthr
Version : 1.0 1.1 2.0 7.0 97
Platform : WINDOWS
Issue type : kbhowto
|