Year Month Day
By Richard Rost
4 years ago
Year, Month, Day Functions Get Parts of a Date
In this Microsoft Access tutorial, I'm going to teach you how to get the various components of any valid date field using the Year, Month, and Day functions. We'll see some examples of how to do things like generate a list of all records from the current year, the previous year, the current year and month. You'll see how to get a list of people with birthdays on a specific date, and lots more.
Pre-Requisites
Recommended Courses
Links
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, year function, month function, ms access year function, year month function, year, MS Access Year() Function, day function, How do I get the month from a date, How do I get the day from a date, How do I get the day from a date, from this year, from last year
Subscribe to Year Month Day
Get notifications when this page is updated
Intro In this video, we will talk about the Year, Month, and Day functions in Microsoft Access and how you can use them to extract parts of a date from your tables and queries. We will look at creating calculated fields to pull out the year, month, and day values, setting up criteria to filter records by specific dates, and using parameter queries to let users specify which year to search for. You will also learn tips for working with date formats, sorting, and how to find records like birthdays or anniversaries using these functions.Transcript Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, we are going to talk about the Year, Month, and Day functions and how to use them to get parts of a date in Microsoft Access. A lot of the time with Microsoft Access, it is very handy to be able to get a piece of a date. You might want to know the year, the month, or the day that a bunch of dates fall on, for example. Let's say you want to see all the records from the year 2000 or you want to see all the people who share a birthday with you. We can get that information using the Year, Month, and Day functions.
Before we get started, you should know how to build Access queries, and you should know how to set criteria for those queries. You should know what a calculated field is and how to create one. If not, go watch these two videos. They are free. They are on my website, and they are on my YouTube channel. You will find the links down below, so you can watch them and come on back.
Here is my TechHelp free template. This is a free database. You can download a copy from my website if you want to. I have got a Customer table, and in that Customer table, I have got a CustomerSince field. But this could be any data. It could be a birthday, an anniversary, an order date, it does not matter. Let's use this to get some information.
I am going to close this table. Let's create a query. We do most of our calculations right inside of queries. Queries are great for this kind of stuff. I will bring in my Customer table, and in here, I just want to see my first name, my last name, and my CustomerSince field. There it is. That is what I have got so far.
Now, let's pull out the year, the month, and the day of this date.
There are a bunch of different ways to do it. There are a lot of different functions. I will talk about some different ways at the end of this video and give you some links to other stuff you can watch, but my favorites are Year, Month, and Day. They are that simple.
I am going to click right down here and create a calculated field. I am going to hit Shift+F2 to zoom in so you can see better. I am going to make a new field called Y, and that is simply going to be the Year of CustomerSince. That is my field. That is it - the Year of CustomerSince. Hit OK.
Now Access puts brackets around it. Let me zoom in again so you can see it. Access puts those brackets around there. That is okay. I do not use spaces in my field names, which I teach in my Access Beginner 1 class, so I do not have to worry about that.
Now, if I run this, there is the year. See, the Year function pulls the year out of that value there.
We will do the same thing for Month and Day, so let's go back in here. Right-click right down here. I will go M as the Month of CustomerSince, like that. Hit OK, and then the same thing for D. D is the Day of CustomerSince. There we go. Now I have three pieces of that date: the year, the month, and the day.
Now that I have those, I can do all kinds of cool stuff. For example, let's say the boss says, "I need to see all the records from the year 2019." Not a problem.
Let's save this query first of all. It is called MyCustomerQ, and again, this does not matter if it is orders, or contact history, or whatever. You want to see the records from 2019? Come down here to Criteria under your Year column, now your Year field, and type in 2019. That is it. Watch. Run it. There is your 2019 record.
You want to see everything from 2000 to 2019? Well, "Between 2000 and 2019" - that is the BETWEEN keyword. Widen it out so you can see it. Run it.
Want to sort that list? Let's sort it. Sort ascending and there you go.
Want to learn more about the BETWEEN keyword? There you go, there is a video for you. I will put links to all this stuff down below.
Want to ask the user to enter the year? Just put inside square brackets, right in that criteria, "Enter the year", just like that, inside square brackets. I will zoom in for you, just like that. Run it now and look at that: Enter the year. I will type in 2013 and there are your records from 2013. This time, every time they run the query, it will ask them for the year. 2003. There you go. That is called a parameter query, when you ask the user to enter in some parameter value.
What if you want to see all the records from the current year?
Come down here in the criteria again. I will zoom in. I want the year of the records to be equal to the year of the current date, so in here put =Year(Date), like that. That is the Date function. That will return today's date and send it to the Year function, which gets you just the year from today's date.
Hit OK and then run it. I do not think I have anybody in here. Let's cheat. Let me go back to my Customer table. Yeah, I don't have any 2022s in here. Let's cheat. Let's make this guy 2022 and let's make this guy 2022.
I am using ISO dates, by the way. It is year-month-day because I have students all over the world, and this is a date format that works for everybody. If you want to learn more about that, there is a video for you.
Go back down and run that query again. Run and there we go. As you can see, it pulls out the current year.
If you want to go back a year, show me all the records from last year, you can just come down here and say the year of that date minus one. That will subtract one from it and there is our 2021 record.
If you want to specify the year and month, let's say the boss wants to see all of the orders from this year, 2022, and this month, eight. Run it now, and there you go. There are all the orders from this year and month. Remember, criteria work across (going AND), and down (going OR), so if I wanted to see 2022 and 2019, I could put them like that. Let's get rid of the month. This will show me 2022 or 2019.
If I add on an eight over here, this has to be 2022 and August, or all of 2019. That is how that works.
I covered this kind of query criteria in my Access Beginner Level 5 class. We do all kinds of crazy situations with BETWEENs and ORs and ANDs and all that stuff.
What if you want to see this year and this month, so the query does not ever have to change? Again, =Year(Date) AND =Month(Date), just like that, and you could do the day of the date too if you wanted to. There are all of the records from this year and month.
What if you want to see people who all share the same birthday regardless of year? Let's say August 5th. Put an 8 here and a 5 there and then run it, and there you go. There are all our August 5th babies, or CustomerSince, or orders, or whatever you are tracking.
Again, if you want to do a report that says "Show me all of the birthdays today," =Month(Date) and =Day(Date). Today is August 20th. Let's see if anybody is in here.
Now, I am leaving this mistake in here. I am leaving this in the video because this is a common email I get from people. "Today" is an Excel function. You use "TODAY" in Excel to get the current date. That was just out of habit, I typed "today" because I was doing a bunch of stuff in Excel earlier.
"Undefined function 'today'" means Access has no clue what that is. I am going to leave that mistake in there so you guys see it, because if I make it, chances are you will make it, and based on the number of emails I get, lots of people make it.
We do not have any August 20ths. Let's cheat and put a couple in here. Let's say August 20th, we will just do the one, and we will run it, and there is our August 20 baby.
Speaking of birthdays, I have got a whole video on checking for birthdays. Check this one out. I have got a video for sorting by month, both alphabetically and chronologically, so you can have this sort by the number order (1, 2, 3) or sort them alphabetically by month (August going first, December, and so on).
In addition to pulling out the values with the Year, Month, and Day functions, there is a single function called DatePart, where you can specify different parts of a date, including some weird stuff like the week of the year, the quarter, and that kind of stuff. So, look at the DatePart function if you want some of that stuff too.
You can compare two years to get roughly the difference between them. If you need exact ages, like if you want to check and see if someone is 21 before they can drink, check out my age video. Just because the years are the same, you might be born in October, but it is only January or the current year, so you have not turned that age yet. Check this video out if you want to calculate ages.
Year-to-date functions? Check this one out if you want to do year-to-date, like sales year-to-date.
You want to learn more about date math and how Access actually works with dates behind the scenes under the hood? Check this video out, for example, if you want to add a week, you can just add seven to a date field. There are all kinds of cool little tricks.
The Year, Month, and Day functions and DatePart will help you pull a date apart, but you can also use the DateSerial function to put them back together again. That is where you can say, "Okay, I know the year and the month and the day. I want to smash those back together and make an actual valid date field out of it." You can use that for birthdays too, because if you know their day and month and the current year, you can put that all together and get a valid birthday date for this year. That is covered in this one.
As you can see, I have got tons of videos on working with dates in Access. It is one of the most important things. This one here, Format - if you are displaying a date value either on the screen or on a report, you can use the Format property or the Format function to display how you want to. Be careful because Format will actually turn the date field into a string, a text string, so sometimes you lose the ability to do things with it, like further calculations on that date. Check this video out too.
Finally, if you really want to learn all the rest about working with dates in Access, my Access Expert Level 27 and 28 classes go over it. It is a two-part series on nothing but date and time functions, all kinds of stuff. There is 27, there is 28, all these different functions we go into. We tear all the date functions apart. It is like four hours long, so check that out. I will put links to all this stuff down below.
There is your Fast Tips video for today on the Year, Month, and Day functions. I hope you learned something. I will see you next time.Quiz Q1. What is the primary purpose of the Year, Month, and Day functions in Microsoft Access?
A. To format date fields for printing
B. To extract specific parts of a date such as year, month, or day
C. To convert text to a date data type
D. To validate date values
Q2. Where are calculations like extracting the year or month from a date usually performed in Access?
A. In reports
B. In tables
C. In queries
D. In forms only
Q3. Which of the following is the correct way to create a calculated field to display the year from a date field called CustomerSince?
A. Year: [CustomerSince]
B. Y: Year([CustomerSince])
C. Year([CustomerSince]): Y
D. CustomerSince: Year
Q4. How can you prompt a user to enter a specific year as a criteria in an Access query?
A. By using the input function
B. By placing [Enter the year] in the criteria box
C. By adding a new text box in a form
D. By entering "Prompt" in the query criteria
Q5. What does =Year(Date) return when used as a query criteria?
A. The month number of the current date
B. The current year from today's date
C. The formatted current date
D. The current day of the month
Q6. What should you do to filter records for both a specific year and month in an Access query?
A. Use BETWEEN with year and month values
B. Enter both the desired year and month as criteria for their respective calculated fields
C. Use only the year as the criteria
D. Add an OR condition with both years
Q7. If you use =Year(Date)-1 as your query criteria, what data does the query return?
A. Records from next year
B. Records from two years ago
C. Records from the current year
D. Records from last year
Q8. Which function allows you to extract parts of a date beyond just year, month, or day (such as quarter or week)?
A. DateSerial
B. Format
C. DatePart
D. CDate
Q9. What problem occurs if you use "today" instead of "Date" in Access to get the current date?
A. "today" retrieves tomorrow's date
B. "today" works the same as "Date" in Access
C. Access returns an error stating the function is undefined
D. Access automatically corrects it to "Date"
Q10. What does the DateSerial function do?
A. Splits a date into year, month, and day
B. Converts text into date format
C. Builds a new date from specified year, month, and day values
D. Changes the format of a date to text
Q11. What caution does the instructor give about using the Format function on date fields?
A. It can reduce query speed
B. It always removes the time part
C. It may turn the date field into a text string
D. It changes all dates to the current year
Q12. When checking for all records from people with the same birthday regardless of year (for example, August 5th), what criteria values should be used?
A. Use 5 in the Year field and 8 in the Day field
B. Use 8 in the Year field and 5 in the Month field
C. Use 8 in the Month field and 5 in the Day field
D. Use 8 in the Year field and 5 in the Year field
Answers: 1-B; 2-C; 3-B; 4-B; 5-B; 6-B; 7-D; 8-C; 9-C; 10-C; 11-C; 12-C
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 the Year, Month, and Day functions in Microsoft Access, which are very useful when you need to extract parts of a date from your database fields. Often, you will want to know specific details, such as the year, month, or day from a given date. For example, you might want to pull up all records from a certain year or identify people who share your birthday. These tasks become much simpler when you know how to use the Year, Month, and Day functions.
Before starting, it is important that you are comfortable building queries in Access and setting criteria within those queries. You should also be familiar with calculated fields and how to create them. If you are not, I recommend watching the beginner tutorials that are freely available on both my website and YouTube channel. You will find the links provided below this article.
For this demonstration, I used the TechHelp free template, which is available for download on my site. In the Customer table, there is a CustomerSince field that serves as a good example, but any date field (like a birthday, anniversary, or order date) can be used for this purpose.
The first step is to create a query and bring in the Customer table. In the query design, select the fields you want to see - in my case, the first name, last name, and CustomerSince. To extract the individual portions of the date, add calculated fields for Year, Month, and Day. This is done by creating new fields in the query grid that use the Year, Month, and Day functions on the CustomerSince field. For example, a field named Y will use the Year function to pull out the year, M will use the Month function, and D will use the Day function. If you run the query, these calculated fields will show the separate year, month, and day values for each record.
With these values available, a number of things become possible. If your boss needs to see all records from the year 2019, you can place 2019 in the Criteria row of the Year column to filter for that year. You can also use the BETWEEN keyword to retrieve all records in a given range, such as from 2000 to 2019. Sorting by year is straightforward in the query design by using the Sort option.
If you want users to specify the year themselves, use square brackets in the criteria (for example, [Enter the year]). This will prompt the user to enter a value when running the query, making it a parameter query. To filter by the current year dynamically, you can specify that the Year of the date equals Year(Date), where the Date function returns today's date.
The same approach is used if you want to filter for the previous year by subtracting one from Year(Date), or to combine year and month filters by entering criteria for both fields. You can also use these fields to match specific month and day combinations, such as all customers born on August 5, regardless of year. To automate reports for birthdays occurring today, use criteria that match both the month and day portions to the current date. If you make a mistake by using "today" (as you might in Excel), Access will not recognize this function and you will see an error message, since Access uses "Date" instead.
The video also highlights related features and videos. For sorting by month, either alphabetically or chronologically, there are additional tutorials available. There is also the DatePart function, which lets you extract portions of a date beyond year, month, and day - such as week number or quarter.
If you need to compare years or calculate things like age, it is important to remember the calculation might not be perfect unless you account for both the month and day (as just comparing the years will not be accurate for someone's exact age). I have a separate video on age calculations for more details.
For aggregating data like year-to-date totals, there are specific techniques and videos on how to approach that in Access. Understanding how Access stores and calculates with dates can be very useful, especially when you need to perform date math such as adding weeks or other time intervals.
Not only can you decompose a date with these functions, but you can also put dates back together using the DateSerial function. This can be useful if you want to create a date from separate year, month, and day values. That approach is great for tasks like checking who has a birthday this year.
The Format function can help you display dates in a particular way for forms and reports, but keep in mind it will convert your date to a text value, so further calculations will not work as expected. It is important to be aware of this when formatting.
If you are interested in a deeper understanding of dates in Access, my Expert Level 27 and 28 classes provide an extensive overview, covering just about everything you might want to know about working with dates and times.
That covers today's Fast Tips video on the Year, Month, and Day functions in Microsoft Access. If you want to see a complete, step-by-step demonstration, you will find the video tutorial and all the supporting materials on my website at the link below. Live long and prosper, my friends.Topic List Using Year, Month, and Day functions in Access queries
Creating calculated fields to extract date parts
Applying criteria to filter records by year
Filtering records between two years using BETWEEN
Sorting records by extracted year values
Prompting users for year input with parameter queries
Filtering records for the current year using Year(Date)
Filtering records for the previous year with Year(Date)-1
Filtering records by specific year and month combinations
Filtering records for this month and this year dynamically
Filtering records matching a specific month and day
Filtering records for today's birthdays using Month(Date) and Day(Date)
Understanding query criteria with AND and OR logic
Using DatePart to extract different date components
Using DateSerial to combine year, month, and day into a date
Using Format to control the display of date values |
