This is a common issue not just in Microsoft Access but in many database systems.

Why aren’t my numeric values sorting properly?

 

The Heart of the Matter

The Short Answer: Your column is defined as a text field rather than a numeric field!

When sorting, numbers in numeric fields sort by value (1, 2, 11, 15, 22…).

But numbers stored as text sort lexically (like words), so “108” comes before “2” because it sorts character by character.

If you store numbers in a text field, the sorting will never behave like true numeric sorting.

This highlights the importance of choosing the correct data type during database design. Normalization is important, but so is setting proper column types.
 

The Workaround

If you can’t change the data type immediately, you can force numeric sorting using SQL functions:

SELECT MyTable.MyField
FROM MyTable
ORDER BY Val(MyTable.MyField);

SELECT MyTable.MyField
FROM MyTable
ORDER BY Format(MyTable.MyField,"00000");

SELECT MyTable.MyField
FROM MyTable
ORDER BY Right( "00000" & MyTable.MyField, 5);

Notice the alignment of the values, they remain text values which may, or may not be desired. If you want the output to be numeric, then you could do:

SELECT Val([MyTable].[MyField]) AS MyFieldValue
FROM MyTable
ORDER BY Val([MyTable].[MyField]);

Now, if you are truly dealing with a mixed content column (so both numeric and textual contents), then you could always do something along the lines of:

SELECT 
    MyField
FROM MyTable
ORDER BY 
    IIf(IIf(IsNumeric(MyField), Val(MyField), Null) Is Null, 1, 0),  
    IIf(IsNumeric(MyField), Val(MyField), Null),                      
    MyField;

 

The Proper Fix

The best and most reliable fix is to change the column’s data type to numeric:

1. Rename the existing text column
2. Add a new column with the same name but a numeric data type
3. Use an update query to convert and copy the values:

   UPDATE MyTable SET NewNumericField = Val(OldTextField);

4. Verify the data and then delete the old text column
5. If this is in Microsoft Access, now’s a great time to perform a Compact and Repair

This approach ensures consistent, accurate numeric sorting and better database integrity.