Alpha / numeric sort

Hi,

I have a column that has values like:

AU-10, AU-8, AU-9, AU-10A, AU-10AA, AU-11, AU-12, AU-12B, B-11, B-11C

When i sort them using order by

I get the result as:

AU-1
AU-10
AU-10A
AU-10AA
AU-11
AU-12
AU-12B
AU-8
AU-9
B-11
B-11C

However, I need the result as:

AU-1
AU-8
AU-9
AU-10
AU-10A
AU-10AA
AU-11
AU-12
AU-12B
B-11
B-11C

If any one can tell how to do it, it would be a lot of help.

Thanks,
Ven80

Hi Ven,

I had a similar problem and I solved it by adding another field for sort order.
Field1 | Field2

AU-1 | 1
AU-10 | 10
AU-10A | 10.1
AU-10AA | 10.11
AU-11 | 11
AU-12 | 12
AU-12B | 12.2
AU-8 | 8
AU-9 | 9
B-11 | 11
B-11C | 11.3

Is the only way I know of... well that is unless you want to edit each other records....

AU-01
AU-08
AU-09

======

But I am anticipating you don't want the zero's displayed so when displaying, just replace(rs("field"),"-0","-")

====
My issue was with apparel and size variants (4-s,m,l) and I assigned each variant an option number so the computer would see 4=1,2,3 etc.

Good luck.

Have a look at the attached database, best I can come up with.


regards,

I got it sorted out myself. Anyway thanks for the suggestions made.
Soky, my solution was the same as your second suggestion.

and ansentry, I could not open the file you sent.

Anyway thanks a lot to you guys.

Ven.

The zip file I posted was created using Access97, what version of Access are you using?

I have attached both Access97 and will post Access2000 next.


I am very surprised that you could not open either the zip or access files.

Here is 2000