Sort by Month
By Richard Rost
4 years ago
Sort by Month Alphabetically & Chronologically
In this Microsoft Access tutorial, I'm going to show you how to sort by the month of any date, both alphabetically and chronologically, using the Month and MonthName functions.
Pre-Requisite
Links
Recommended Courses
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #fasttips, month, monthname, Sort months logically and not alphabetically, Sort records based on partial values in a field, Sorting by month in MS Access, group by month in ms access query
Subscribe to Sort by Month
Get notifications when this page is updated
Intro In this video, I will show you how to sort records by month in Microsoft Access, both chronologically by the month number and alphabetically by the month name. We will use the Month and MonthName functions to group items like birthdays or anniversaries regardless of the year or day and set up a query to display and sort these results as needed. I will also explain how to use calculated fields to handle sorting issues that can arise when working with these functions.Transcript In this video, I'm going to show you how to sort by month both alphabetically and chronologically using the month and month name functions.
Here is a question I get asked a lot: How do you sort a list of records by month? Whether you are dealing with birthdays and want to sort them to get all the Januarys together, all the Februarys together, or you are working with anniversaries or something else, it does not matter what the year or the day is. You just want all the people from the same month together.
We are going to start off by isolating just the month from each customer's date, whatever date it is. If you look here in my customer table, for example, I have a field called Customer Since that represents the date that they started being a customer, when you put them in your database. It could just as easily be a birthday or an anniversary or anything else.
Let's say I want to sort these by the month so I can send out a sales card or a "thanks for being a customer so long, happy fifth anniversary" kind of card.
Let's make a query. Create query design. I'll bring in my customer table. I just want to see the Customer ID, First Name, and Last Name, and let's bring in that Customer Since field. Where are you? There you are. That is what I have so far.
The first thing I want to do is pull out just the month. To do that, I'll use the Month function. I'm going to create a new calculated field. I'm going to zoom in so you can see better with Shift-F2. I'm going to call this field M and it is going to represent the month of whatever field it is, Customer Since, just like that. Hit OK, and of course, since I do not have any spaces in my field names, Access puts those brackets around there for me automatically. That is okay.
If I run that, you can see this represents just the month portion of that date. That is the Month function. That's why we do not use words like Month or Day or Year as our field names because those are reserved words. There is a big long list on my website. I'll put a link down below if you want to see them all.
If I wanted to sort by the month chronologically, I could come in here and sort ascending. Now, when I rerun this, it does not matter what year they are from. I got all the ones together, all the twos together, and so on.
If you did want to sort by this and then by this, so that if you have two from the same year, for example, they show up in chronological order after that, just switch their order. Put the M first and then Customer Since, and you can sort that ascending after it. So, we will sort by this and if you have two from the same year, let's see if we have any. Now, the exact same date though, let's change this one. So it is 10/10 from 2003. Let's rerun the query. 2003. See, yep, 1/02, 10/10. Okay.
Some people have asked if they could sort it alphabetically based on the name of the month. So you want April on top, for example. To do that, we can use the MonthName function.
Let me zoom in again. M and MonthName is going to be MonthName, and then it wants a number from one to twelve. So give it M. That is the number we already figured out from over here. Run it and there you go.
If you want the abbreviation, you can either take the left three characters of that using the Left function, or MonthName has a second parameter. If you say comma true here, it will abbreviate. Run it now and there you go.
If you want to sort based on this, you cannot just come in here and turn sort ascending on here and turn it off over here because watch what happens. They get an Enter Parameter Value. That is because at the time you are trying to sort this, this has not been evaluated yet because it wants to do this first and it does not know what M is.
Sometimes things that work normally do not work once you put other stuff on, other conditions or criteria or sorts. It is just the way that Access evaluates stuff. So what you have to do is we are going to take what M is - this Month(CustomerSince), copy that, come over to the MonthName function and instead of relying on getting M here, delete that and just paste that in there. So it is going to evaluate the month of Customer Since first, then send that number to the MonthName function, and there is your month.
Now we can get rid of this guy or leave it if you want to see it is there. Now remember you have to move this to the left, because it sorts left to right. Run it and there you go. You are sorted alphabetically by the name of the month, if that is what you want. If those are matched up, it will sort by that. See how that works. Pretty straightforward stuff.
I cover lots more about working with time and date functions in my Access Expert Level 27 and 28 classes. We go over all kinds of different date functions, how to work with them, how to display numbers like ordinals, all kinds of different stuff. There is a lot of material, about four hours, just on date and time functions. So check it out. I'll put a link down below.
If you have any questions, post them in the comments section.
Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
How do you become a member? Click on the Join button below the video. After you click the Join button, you will see a list of all the different membership levels that are available, each with its own special perks.
Silver members and up will get access to all of my extended cut TechHelp videos, one free beginner class each month, and more.
Gold members get access to download all of the sample databases that I build in my TechHelp videos, plus my Code Vault where I keep tons of different functions that I use. You will also get a higher priority if you decide to submit any TechHelp questions to me, and you will get one free expert class each month after you have finished the beginner series.
Platinum members get all the previous perks plus even higher priority for TechHelp questions, access to all of my full beginner courses for every subject, and one free developer class each month after you finish the expert classes. These are the full length courses found on my website, not just for Access. I also teach Word, Excel, Visual Basic, and lots more.
You can now become a Diamond Sponsor and have your name or company name listed on a sponsors page that will be shown in each video as long as you are a sponsor. You will get a shout out in the video and a link to your website or product in the text below the video and on my website.
But do not worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I will keep making more and they will always be free.Quiz Q1. What is the main purpose of the Month function in Access as demonstrated in the video?
A. To extract only the month portion from a date field
B. To display the month name based on a number
C. To sort records alphabetically
D. To abbreviate month names
Q2. Why should you avoid using words like Month, Day, or Year as field names in Access?
A. They are reserved words in Access
B. They are too long for field names
C. Access does not allow date-related field names
D. They cannot be sorted alphabetically
Q3. If you want to sort records chronologically by month regardless of year, what should you do after creating a calculated Month field?
A. Apply an ascending sort on the Month field
B. Delete all year information from the date field
C. Sort by customer name first and then by month
D. Only view the data in Form View
Q4. How can you display the full month name instead of the month number in a query?
A. Use the MonthName function with the month number as the parameter
B. Use the Year function
C. Use the DateName function with the date field
D. Use the Left function only
Q5. If you want the month abbreviation (like Jan, Feb, etc.) instead of the full month name using the MonthName function, what should you do?
A. Add a second parameter as True in the MonthName function
B. Only use the Left function with three characters
C. Remove the Month function entirely
D. Use the DayName function
Q6. Why might sorting alphabetically by month name in Access give you an 'Enter Parameter Value' error when referencing a custom field like M?
A. Access evaluates sorts before calculating derived fields
B. The MonthName function only accepts numerical fields
C. The Customer Since field is missing
D. Sorting is only possible in Table View
Q7. What is the correct way to sort by month name alphabetically if referencing a calculated field causes an error?
A. Place the entire Month function inside the MonthName function directly
B. Remove all calculations from the query
C. Sort by the original date field
D. Rename all fields to remove spaces
Q8. When organizing sorting fields in a query, why does the order of fields matter?
A. Sorting is processed from left to right
B. Field names determine their priority
C. Only the first field is considered for sorting
D. The rightmost field always sorts first
Q9. What will Platinum members get in addition to all other perks mentioned for lower-tier memberships?
A. Access to all full beginner courses for every subject taught by the instructor
B. The ability to attend live in-person classes
C. One free beginner class per month only
D. Access only to sample databases
Q10. Why are the free TechHelp videos promised to remain available?
A. To ensure everyone can keep learning regardless of membership
B. Because members are the only ones who can access them
C. So only Gold and Platinum members benefit
D. To reduce instructor workload
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-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 video from Access Learning Zone focuses on how to sort by month in Microsoft Access, both by the order of the months from January to December and alphabetically using the Month and MonthName functions.
This is a question I get quite often: how do you group records by month, for example, to bring all the January birthdays together, or perhaps all the February anniversaries, regardless of the year or the exact date? You might need this if you want to reach out to customers who joined in a specific month or send out reminders and special cards for anniversaries.
To start, you first need to extract the month from the date field in your table. As an example, in my customer table, there is a field labeled Customer Since, which shows when someone became a customer. This could just as easily be a birthday or any other significant date.
Suppose you want to organize these customers by the month they joined, maybe to send out a card or a special anniversary greeting. First, we create a query and add the necessary fields from the Customer table: Customer ID, First Name, Last Name, and Customer Since.
Next, we use the Month function to pull out just the month from the Customer Since field. This involves adding a calculated field to our query that returns only the month part of the date. Access handles field names with spaces by putting brackets around them automatically, which is just fine.
When you run the query, you will see that this new field gives you just the month number for each record. This highlights why it is important not to use reserved words like Month, Day, or Year as your field names in Access, since these are already part of Access' internal vocabulary.
If you want to sort your records by month in chronological order, simply sort this new month column in ascending order. Now it does not matter what year your data is from, all January records (1), February records (2), and so on, are grouped together.
If you want to further organize records that have the same month, you can also sort by the Customer Since date after sorting by the month number. Just switch the order of the sort fields to prioritize month first, followed by the full date. This would let you see, for example, which customers joined earliest in the same month.
For those who prefer sorting alphabetically by the month's name, such as having April at the top, you can use the MonthName function. This function converts the numerical month into its name, like February or March. If you prefer an abbreviated version of the month, MonthName supports a second parameter, so by entering True, you get the three-letter abbreviation.
However, when trying to sort directly by this calculated MonthName field, you might run into a problem. Access cannot always evaluate one calculated field that is based on another calculated field in the same query when sorting. The solution is to place the full Month function directly in the MonthName calculation, instead of referencing the earlier calculated field. This ensures Access calculates the month number first and passes it directly to MonthName. After adjusting your query, move the MonthName field to the left so Access sorts based on it first. Now your records are ordered alphabetically by month name.
This covers the basics of sorting records by month, both chronologically and alphabetically. If you're interested in learning more about working with date and time functions in Access, I go into much greater detail in my Access Expert Level 27 and 28 classes. These cover a range of date functions and how to format and display date-related data, with nearly four hours of lessons on this subject. You'll find a link to those courses below.
If you have any questions about anything discussed here, feel free to leave them in the comments.
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 Isolating the month from a date using the Month function
Creating a calculated field for the month in a query
Sorting records chronologically by month
Sorting by multiple fields for detailed chronology
Using the MonthName function to display month names
Abbreviating month names with the MonthName function
Correctly sorting alphabetically by month name in Access queries
Resolving sorting errors with calculated fields in Access
Adjusting query field order for sorting by month name |
