Incorrect sort order

[Chronus13]

Hello everyone. First, i am trying to sort a column that is alpha numeric. Examples look like this: A-1,A-2,A-3,A-4,A-10,A-11. In my tables I don't really care how they look because they are Primary Keyed by a different number that never is shown outside the table. I use that table and made a query, still here the sort does not matter. I then create a report that has these in a column and it always sorts A-1,A-10,A-11,A-2,A-3,A-4. From what I have found on the internet is there is no way to actually make this work unless I create ALOT of code. I am hoping there is an easier way. Any ideas?

[Welshgasman]

No, just split the alpha from numeric and sort alpha, then numeric.

[ranman256]

In extra column convert it to a number:
Cint(mid([field], instr([field],"-")+1))

then sort

[Chronus13]

OK, I am sure I probably just don't see why it isn't working. I took the locations..A-1, A-2 and the rest and split them up into two separate columns (Drawer-A) and (Item-1, 2, 3 etc.) and the numbers are still sorting incorrectly putting the 10 before the 2 for example.

[Micron]

Because your field data type is text and not a number data type? This happens when the numbers are text because anything that starts with 1 comes before that which starts with 2. So
1
11
111
2
21
etc.
If you did this in a query, you'll have to coerce the data type as was stated in post 3.

[Welshgasman]

So surround the numeric field with Val() as it sounds like it is still text?

[Chronus13]

OMG! I forgot to change it to a number in design view lol. Thanks!

[Micron]

As I stated in other threads, val stops reading a string as soon as it encounters a character than it cannot recognize as a number or a character that pertains to numbers, such as the decimal separator. However, commas as separators are not recognized. Anyway, Val(A-1) will return zero in a query where the field contains A-1.