Natural Sorting
By Richard Rost
2 years ago
Natural Sorting in 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?
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.
Members
There is no extended cut, but here is the database download:
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Recommended Courses
Keywords
TechHelp Access, 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
Intro In this video, I will show you how to perform natural sorting in Microsoft Access, so your text fields that include both numbers and letters (like 1a, 2c, 100a) are sorted the way you expect. We'll talk about the difference between alphanumeric and natural sorting, how to set up a table, use the VAL function in a calculated query field, and sort your data numerically before considering the letters. You'll also learn how to handle non-numeric characters and save your sorted query results for future use.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost. Today we're going to talk about natural sorting. What is natural sorting? Well, it's sorting the way that you think sorting should be. And if you've got a text field that has numbers and letters in it, and you want to sort those numbers the natural way, well, that's what we're going to do in today's video.
Today's question comes from Clara in Bowie, Maryland, one of my platinum members. Clara says, "I receive lists of unit numbers that contain both numbers and letters, such as 1a, 1d, 2f, 571, 01d, etc." And not 101 and 101d. The big difference between an O and a zero when you're talking about computer stuff. 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 letters?
Well, that is exactly what natural sorting is. If you look at this list here, this is alphanumerically sorted. What the computer basically does is it just looks at the columns. It sorts it by the first column, then by the second column, then by the third column. And numbers come first, so zero would come before a, which comes before b, right, and then 2, and so on. But the 105 is sorted in with the ones.
So what you want is a numeric sort, but you want to ignore the letters. Okay. And to do this, we can use one of my great friends, the Val function, which stands for value. Today we're going to use a calculated query field. So if you're not familiar with how to create a calculated field, go watch this video first, then come on back.
Okay, here I am in my TechHelp free template. This is a free database. You can download it on my website if you want to. But we're just going to close this and we're going to make our own table. So create table design. And I always start off with an ID field of some kind. It doesn't matter for this example, right? And then we'll make a field. Let's just call it my field.
Now, if you're going to have numbers and letters in the same field, this has to be short text. You can't make it a numeric type, right? All right. So save that. We'll just call it my_t for my table. And let's put some values in it. So I'm going to put in here: 1, 2, 3. And then I've got 1a, I've got 2c, I've got 100a, right? 205f and 1001g, whatever.
Okay. Now, if I sort this list, right click, sort, you can see there is your alphanumeric sorted list. It looks at the first column, the second column, and so on. And that's how they're sorted. So what we're going to do is we're going to create a query now. Save changes to this guy. Sure. Create query design. And I'll bring in that table, right? my_t right there. Close this guy. All right. I'll bring in ID and I'll bring in my field.
We're going to make another calculated query over here, a calculated field, that is. And I'm going to zoom in so you can see better, shift F2. Okay. And we'll call this field, it doesn't matter, we'll call it x, right? x: and it's going to be the value VAL of my_field, just like that. Value is a function. We're going to send my_field into it. And what value does is it's going to take the alphanumeric part off the left side of the field. All right. Let me show you. Ready. Run it. And there you go. So you've got 1, 2, 3, 1, 2. All right. 100, 205, and 1001.
Now there are some rules with VAL. All right. VAL calculates the value of a string starting from the left and continuing until it encounters a non-numeric character or a period. Okay. So 123ABC returns 123. It stops when it hits that A. 45.67x gives you 45.67. Okay. 1A2B3C gives you a 1 because it stops once it hits that A.
Now, dollar signs and commas are considered non-numeric. So if you send it something like $14, you're going to get a zero because it didn't find any numeric characters before it hit that dollar sign. All right. 100, two. All right. Bring you 100. This might be different if your regional settings are set to countries that use commas for decimal places. I'm not sure. I don't know. I've never done it. So if someone could post in the comments down below and let me know, it probably sees the comma as the decimal placeholder.
Okay. All right. So now that you got this x, now we can sort based on this column. So what I'm going to do is go to design view. We're going to move x to the left of my_field because remember sorting works left to right. So I'm going to change the sort right here, drop this down and pick ascending. And then if you want to also sort by this guy, so if you've got, you know, three ones in a row, it'll then sort by my_field. Then you can do this as well. And now if you run it, there you go. There's your numeric values. And then this field is now sorted properly numerically with a natural sort. All right. All the ones are together. Two, three, 100, and so on.
Okay. And now you can just save this query as my_q. And you're pretty much done. That's it. So that's pretty much it. That's going to be your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time.
A special thank you and shout out to our diamond sponsors. First, we have Juan Soto with Access Experts Software Solutions, manufacturing experts, specializing in Access and SQL Server. Juan is a 13-time Microsoft Access MVP. You can check them out at accessexperts.com.
Another shout out to Sammy Shama from Shama Consultancy. Sammy is a certified Microsoft Office specialist. And he not only offers Access Application Development, but he also provides one-on-one tutoring services. So if you need someone to hold your hand and help you with your Access Project, Sammy is your guide. Check them out at shamaconsultancy.com.
TOPICS:
Natural sorting
Creating a table in Access with text and numeric fields
Differences between alphanumeric and natural sorting
Using the VAL function in Access
Creating a calculated query field in Access
Sorting data numerically using a calculated field
Handling non-numeric characters with the VAL function
Sorting results in Access query based on calculated fields
Saving and naming queries in Access
COMMERCIAL:
In today's video, I will teach you about natural sorting in Microsoft Access. We'll tackle how to sort text fields containing both numbers and letters to get the order you intuitively expect. I will walk you through setting up a table, creating a query, and using the VAL function to sort values numerically and alphabetically. Whether it's 1a, 2f, or 1001g, you'll see how to get it sorted just right. Don't forget to check the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.Quiz Q1. What is natural sorting?
A. Sorting numbers and letters alphanumerically
B. Sorting numbers by numeric value and then letters
C. Sorting based on the length of the text
D. Sorting by reverse alphabetical order
Q2. What problem was Clara experiencing with her unit numbers?
A. The unit numbers were not appearing in alphabetical order
B. The unit numbers were appearing in reverse numerical order
C. Alphanumeric sorting made 100 come before 2
D. The unit numbers were appearing with duplicates
Q3. Why can't a field containing both numbers and letters be set to numeric type?
A. Numeric type fields do not support sorting
B. Numeric type fields only store numbers
C. Numeric type fields automatically exclude letters
D. Numeric type fields slow down the database
Q4. What does the Val function do in the context of sorting?
A. It extracts and returns only the numeric part of a string
B. It converts a string to uppercase before sorting
C. It replaces letters with their ASCII values for sorting
D. It sorts strings in descending order
Q5. What rule should you remember about the Val function's behavior?
A. It continues until it finds a number
B. It stops once it encounters a non-numeric character or a period
C. It ignores any numeric characters
D. It always returns the highest numeric value
Q6. How should you configure the calculated query field to achieve natural sorting?
A. Create a new field and use the Val function to process the original field's values
B. Use the computed field to concatenate the original values
C. Sort the field values in alphabetical order first
D. Filter out all the non-numeric characters before sorting
Q7. Which fields should be used in the query to ensure both numeric and alphanumeric sorting?
A. Use only the original text field for sorting
B. Use the calculated field for numeric values and the original field for secondary sorting
C. Use the calculated field for primary sorting and ignore the original field
D. Use two different queries, one for numbers and one for letters
Q8. Why should the calculated field be moved to the left of the original field in the query design view?
A. Because Access sorts left to right
B. Because it makes the database run faster
C. Because it filters out duplicates
D. Because it adds all numeric values first
Answers: 1-B; 2-C; 3-B; 4-A; 5-B; 6-A; 7-B; 8-A.
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone covers the topic of natural sorting in Microsoft Access. Many of us have encountered situations where we need to organize lists that contain both numbers and letters in a way that feels logical to people, but Access sorts them strictly alphanumerically. This is where natural sorting comes in.
The specific challenge I am addressing was inspired by a question I received from a student who often works with lists containing unit numbers such as 1a, 1d, 2f, 571, and 01d. The issue is that Access sorts these values alphanumerically, which means "100" appears before "2" since the sort is performed character by character, starting on the left. For most people, that does not feel right. The goal here is to sort numbers in a way that treats their numeric values properly, grouping similar numbers together and not just by their alphanumeric order.
To solve this problem, I recommend using the Val function in Access. This function looks at a text value from the left and grabs as much of the numeric portion as it finds, stopping when it encounters a non-numeric character or a decimal point. It will ignore anything that follows that first series of numbers. For example, with "123ABC", the function pulls out "123". If you have "45.67x", you get just "45.67". If your field starts with something like "$14", Val will give you a zero because a dollar sign is not a numeric character. It's also worth noting that your regional settings might affect how certain delimiters, like commas, are treated, especially in countries where commas are used for decimals.
I walk through creating a table to demonstrate. You start by designing a table and adding an ID field, along with another field (let's call it "my field") for the values you need to sort. Since these values can contain both numbers and letters, be sure the field type is short text, not numeric.
After entering some sample records, I show how sorting the table directly still gives you the unsatisfying alphanumeric order. To fix this, you need to set up a query. Add your table to the query, and bring in your fields. Then, create a calculated field in your query design grid using the Val function on the field with your mixed values. The new calculated column will extract the numeric portion from each field.
Once you have this additional column, you can sort by it in the query. It's helpful to move this calculated column to the left in the design grid because Access processes sorts from left to right. By applying an ascending sort to your calculated column and then sorting by the original field, you get a natural sort order: numbers are organized by their real values and then by any trailing letters. For example, entries like 1a, 1d, 2f, and 1001g will now appear in logical numeric order.
Once you've run your query and confirmed the order is what you expect, save the query for future use. This simple technique is very powerful for handling fields that mix numbers and letters, and it allows your Access reports or forms to show lists in an order that makes sense.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Natural sorting in Microsoft Access
Creating a table with text fields for mixed values
Entering sample data with numbers and letters
Understanding how Access sorts alphanumeric text
Using the VAL function to extract numeric values
Creating calculated fields in a query
Sorting query results by calculated numeric fields
Handling fields with non-numeric characters in VAL
Adjusting query design to sort by multiple fields
Saving queries with custom sort orders in Access |
