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

 

Today's TechHelp tutorial from Access Learning Zone is about natural sorting. I'm Richard Rost, and I'll guide you through this concept. Natural sorting arranges data in a way that feels intuitive, especially when dealing with text fields containing both numbers and letters.

Clara from Bowie, Maryland, a platinum member, posed today's question. She receives unit number lists combining numbers and letters, such as 1a, 1d, 2f, 571, and 01d. When sorted in her database, these entries are arranged alphanumerically, causing mismatches like 100 appearing before 2. Clara wants to sort them numerically first and then by letters.

Computers sort data alphanumerically by default, focusing on each column in sequence. This means numbers appear before letters. Therefore, an entry like 105 might be mistakenly grouped with entries starting with 1. To achieve a numeric sort while ignoring the letters, we can use the Val function, which converts a text string into its numeric value.

For our demonstration, we'll create a table in a free template from my website. Starting with an ID field, we'll include a field labeled 'my field' as short text, accommodating both numbers and letters. We'll populate this table with sample data: 1, 2, 3, 1a, 2c, 100a, 205f, and 1001g.

When we sort this data, it's ordered alphanumerically. To implement natural sorting, we'll create a query with a calculated field. This field will utilize the Val function to convert text strings into numeric values by extracting the leading numeric characters.

In this calculated field, we'll define a new field 'x' that applies the Val function to 'my_field.' Running the query, you'll see the numeric parts extracted correctly. The Val function starts from the left of the string and stops at the first non-numeric character or period, ensuring it extracts the correct numeric values.

However, Val has some rules. It parses until encountering a non-numeric character or period. Symbols like dollar signs and commas are considered non-numeric. So a string like $14 will return zero since no numeric characters are found before the symbol.

With our new 'x' column in place, sorting becomes straightforward. We'll sort by this calculated column 'x' in ascending order. Additionally, by including 'my_field' in the sort, we ensure that entries with identical numeric values are correctly arranged by their letters.

Finally, save the query. This approach achieves the desired natural sorting, displaying entries in a numerically logical order with letters correctly sequenced thereafter.

This method ensures your data is sorted in a more intuitive, user-friendly manner. For a complete video tutorial with detailed, step-by-step instructions, visit my website at the link below.

Live long and prosper, my friends.

For a complete video tutorial on this topic, please visit https://599cd.com/NaturalSorting?key=Blogger