How to Perform Natural Sorting in Microsoft Access for Mixed Alphanumeric Lists

In this Microsoft Access tutorial, I will show you how to perform natural sorting for mixed alphanumeric lists to sort numerically first and then by letters, using a calculated query field and the Val function. This will help you sort unit numbers such as 1a, 1d, 2f, 100a, and more correctly in your database.

Clara from Bowie, Maryland (a Platinum Member) asks: I receive lists of unit numbers that contain both numbers and letters, such as 1a, 1d, 2f, 5, 7, 101d, etc. When I sort them in my database, the numbers are sorted alphanumerically, so 100 ends up before 2. How can I sort them numerically first and then by the letters?

BECOME A MEMBER:

LEARN MORE:

PREREQUISITES:

RECOMMENDED COURSES:

FREE TEMPLATE DOWNLOADS:

ADDITIONAL RESOURCES:

KEYWORDS:
TechHelp, Access 2016, Access 2019, Access 2021, Access 365, Microsoft Access, MS Access, MS Access Tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, natural sorting Microsoft Access, Microsoft Access mixed alphanumeric sorting, Access Val function tutorial, sorting alphanumeric lists Access, create calculated field Access, Access query design sorting, Access numeric sorting letters, how to sort unit numbers Access, Access sorting text with numbers

QUESTIONS:
Please feel free to post your questions or comments below. Thanks.
Live long, and prosper.

[CD]

Error: Yes, I'm aware there is an error in my slide around time index 3:38. Periods are included in what the Val function calculates as a number. Brain fart on my part.

[Shadow.Dragon]

Thanks for the very informative video, Richard! Your technique was simple and eloquent.

[katherandefy]

Love all your vids and the courses likewise are topnotch. The slow and easy learn style is just as awesome as the zippy search and destroy it concept. I always liked Access from the first and learning it has been a real fun time. Marvelous! Thank you, Richard!!

[igormalinkov]

One of the rules in databases is to store different types of data in s separate field (number as number and text as short or long text).
In Serbia our decimal separator is a comma and 100, 2 returns 100.

[kennethwdc]

Your slide at 3:57 reads “Val calculates the value of a string, starting from the left and continuing until it encounters a non-numeric character or period.” You give the example of the Val of 45.67x being 45.67. I tried this myself and the Val of 45.67x is 45.67 as your slide shows. If Val stopped at the period (.), as I understand from your definition, it would return 45.

[rtatt]

Hi Richard, @4:00 the "or period" makes no sense for your description of the Val function. Indeed your 2nd example (15.67x) shows that it does continue beyond the period. (Although it would stop at a 2nd period, as that wouldn't make a valid numeric.)