Sort (order) a field that is a combination of alpha & number 1

[jpkeller55]

I have a field of data that is a combination of letter and number and I cannot figure out how to get it ordered correctly.

This is how it is sorting now:
KeyNum
A1
A2
A3
B1
B10
B11
B2
B3[/color red]

I would like to get it to sort like this:
KeyNum
A1
A2
A3
B1
B2
B3
B10
B11[/color blue]


I am guessing I need to split the field into letter and number and sort but don't know how to do that.

Thanks, Jim

 

[Remou]

How about:
SELECT Field1
FROM Table
ORDER BY Left([Field1],1), Val(Mid([Field1],2));

 

[Shirley]

In a new query create two new fields put this code in.

First:Left([FieldName],1)
Second:iif(Count([FieldName]> 2, Right([Field],1), Right([Field], 2))

Try this if the iif is backward just switch the rights around. Then use your sort on these 2 fields.

I hope this helps

 

[jpkeller55]

Beautiful...that works great! Thanks Remou

 

[jpkeller55]

Shirley, thanks for your idea too. I will give it a try.