Access 07 Sorting Alpha numeric

[pboudreau01]

Hello, I created a report in a database which shows a list of assets alphabetically. I have a series of items listed as follows

D-1
D-2
D-3...and so on
D-10
D-11
D-12...and so on
D-20
D-21
D-22...and so on

When I generate the report, it sorts like this...

D-1
D-10
D-11...and so on
D-2
D-20
D-21...and so on
D-3
D-30
D-31...and so on

I am trying to get the report to sort out by alpha then in true numeric order. Can someone offer a solution? Any help would be greatly appreciated.

 

[SkipVought]

hi,

am trying to get the report to sort out by alpha then in true numeric order

It IS!

The STRING

D11

colates BEFORE

D3!!!

To accomplish what you want, you must split the aplha characters and the numeric characters, CONVERT the numeric characters to NUMBERS and then sort them in 2 separate columns.

Or, you could pad the numeric characters with ZEROS like
[tt]
D-01
D-10
D-11...and so on
D-02
D-20
D-21...and so on
D-03
D-30
D-31...and so on
[/tt]
HOWEVER, if your items ever get greater than D-99, then you'll need to pad the THREE places and if the value exceeds D-999, then FOUR places.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dhookom]

You can set the Sorting and Grouping Field/Expression to

=Left([AssetCode],1) & Format(Mid([AssetCode],3),"000000")

Duane
Hook'D on Access
MS Access MVP