Sort An Alphanumeric Column Properly in Microsoft Access

Have you ever been frustrated by the way Microsoft Access sorts text columns that contain alphanumeric content? Consider the following records:

If you were to sort by this column, this is how your final result would look:

Technically, this is correct. Realistically, I’d guess that you were hoping for the results to be ordered 1, 1d, 10a, 14, 24a, 24c, instead. To get the desired result, you’ll need to create a query that splits the content of this column into two parts - one part number, one part letter. Then, sort primarily by number and secondarily by letter. The Microsoft Knowledge Base article “How to Sort Alphanumeric Strings Based on Their Numeric Portions” describes exactly how to create this query. If you follow Microsoft’s instructions, query will look like the one shown here.

Switch the query over to the datasheet view, and here’s the final result: