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