Keeping numbers in an ascending sequence

[partana]

Hello,
I am trying to get double digit numbers to ascend when there is a letter prefix in datasheet view. I have tried the Ascending/Descending function but it doesn’t seem to work for this format.
Example:
A1
A2
A2
A4.... to A9 no problem.
However , when I go to enter A10, A11 they jump to the top of the row
A10
A11
A1
A2
A3....

Can someone explain (simply), how I can make the numbers stay in sequence?
Many thanks

 

[John Big Booty]

The problem is you are sorting a string. to get around this you could add an additional (numeric) sort field, and sort on that rather than the string.

 

[partana]

Sorry,
Can you elaborate a bit more? Do you mean create a separate alphabetic (text) field and a separate numeric field?

 

[Galaxiom]

Derive a field in a query with this in the Field box of the designer:

SortField: Left([mixedfield],1) & Format(Mid(mixedfield],2),"000")

This pads leading zeros on the numeric part. Sort on that field

 

[gemma-the-husky]

in your example A1 is shown below A10 - surely it is actually sorted above A10

anyway - your problem is because, in terms of a "dictionary" sort,

A10 DOES come before A2

hence the solutions are based effectively on sorting the letters, and numbers portions separately.

one way around this is to ensure the fields are fixed width.

so rather than A1, have A01, which will make your sorting consistent up to A99.