Ordering Numeric Values

[kazaccess]

I currently have records that end with a letter and 2 numbers. For example, A1, A2, ... , A10, A11. When I try to sort my table/query by these values, A10 & A11 come before A2. It seems that it is sorting by the first digit shown. Is there any way to fix this quickly within table/query properties so that this can be displayed in proper numeric order?

[orange]

You could try (untested) sorting by Val(yourFieldName). You are getting an alpha sort at the moment.

[lfpm062010]

It is sorting as text field and not number.

To fix it, you have to build a query with just the number portion of the text field. As long as they are consistent (start with letter follow by numbers). This query should fix the issue.

SELECT TABLENAME.SORTFIELDNAME, ...., MID(TABLENAME.SORTFIELDNAME, 2, LEN(TABLENAME.SORTFIELDNAME))
FROM TABLENAME
ORDER BY MID(TABLENAME.SORTFIELDNAME, 2, LEN(TABLENAME.SORTFIELDNAME))

[kazaccess]

Inserting that "order by" statement is giving me the same results. A10-19 are being displayed before A2.
I'm sorry, I should have included this is my description: the full length of these values is the first 2 letters of the day of the week + number. So, for Mondays, this looks like "MoA1", "MoA2", ... , "MoA10", "MoA11"

[orange]

Try along this template --- adjust for your fields and starting position.

SELECT Departments.Departmentname, Val(Mid([departmentName],5))
FROM Departments
Order By Val(Mid([departmentName],5))

[lfpm062010]

Sorry forgot to convert them to numbers and it is the 3rd pisition instead of the second. Since you have 2 characters follow by numbers.

SELECT TABLENAME.SORTFIELDNAME, ...., VAL(MID(TABLENAME.SORTFIELDNAME, 3, LEN(TABLENAME.SORTFIELDNAME)))
FROM TABLENAME
ORDER BY VAL(MID(TABLENAME.SORTFIELDNAME, 3, LEN(TABLENAME.SORTFIELDNAME)))

[kazaccess]

So far, ordering by "MID(TABLENAME.SORTFIELDNAME, 3, LEN(TABLENAME.SORTFIELDNAME))" is getting me the closest to my solution. This gives me "MoA1, MoA10, MoA11, MoA2",etc.

Using VAL() is causing my list to be "MoA5", "MoA9", "MoA8", ... , "MoA3", "MoA10", "MoA11", "MoA2", "MoA1"

Is there some step I may be missing in doing this?

[orange]

Order By Val(Mid(SORTFIELDNAME, 3))