sorting alphanumeric values

[ponneri]

Hi All.

I need to sort a column in a table that is of text type, but has alphanumeric values like

MC10, MC111, MC215 etc.

In the table, when I use the sort from Access ribbon, I get

MC10
MC1112
MC1759
MC101
MC2315
MC3000
etc
etc...

Actually, the MC101 must come between MC10 and MC1112 ! How can I get this done using a query for display purpose ?

Any help is appreciated.

 

[Ranman256]

That is exactly how it is supposed to sort.
If want a numeric sort, make a query and remove the 1st two characters and covert it to a number.
Mid([field],2)

 

[plog]

You really need to store your data like you need to use it. If the 'MC' part and the number part represent 2 different things, then you need to store that value in 2 fields. If every record is prefixed by 'MC', then you shouldn't be storing the 'MC' at all, you should just be storing the numeric part in a numeric field.

 

[gemma-the-husky]

there is something strange here

 MC10
 MC1112
 MC1759
 MC101 
 MC2315
 MC3000
 etc
 etc...

MC101 here SHOULD sort immediately below MC10.

MC18 would sort below MC1759. Is that what you mean?

You want the number portions to sort numerically, within the alpha prefix?

 

[ponneri]

Yes, Dave !

MC101 here SHOULD sort immediately below MC10.

I want the number portions to sort numerically, within the alpha prefix?

 

[gemma-the-husky]

what I meant was, that given the data you posted, there is no way to get the sort order you posted

MC101 will NOT sort in the position you have displayed. - one-zero-one
Are you sure it isn't a LETTER O rather than a number.
MC1O1 with a letter WOULD sort in the position you show. -one-letter O-one

MC10
MC1112
MC1759
MC101
MC2315
MC3000


there will be differences between an alpha sort, and a true numeric sort, but what you are showing isn't one of them.

ie. you will get this

MC10
MC1112
MC12
MC9

when you really want this

MC9
MC10
MC12
MC1112