DateAdd
By Richard Rost
6 years ago
Use the DateAdd Function to Add Whole Calendar Months
In this Microsoft Access tutorial, I will teach you how to use the DateAdd function in Microsoft Access to add or subtract whole days, months, years, hours, minutes, seconds, weeks, or quarters from any date.
Coen from Glendale AZ (a Silver Member) asks: how do I determine what date is exactly six months in the future from a specific date? I can't just add 180 days. It's for a warranty program, so it needs to be exactly six calendar months. Furthermore, if the warranty happens to expire on the weekend, we give them until the following Monday to come in for service. How can I handle that?
Members
I'll show you how to handle part two of the question: if the warranty ends on a weekend, move the date up to Monday. Also, I'll show you how to put "WARRANTY EXPIRED" in big red & yellow letters right on the customer form.
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!
 Links
Format
DateAdd("interval", number, date)
yyyy Years
q Quarters
m Months
d Days
ww Weeks
h Hours
n Minutes
s Seconds
X = DateAdd("m", 1, #1/1/2021#)
Subscribe to DateAdd
Get notifications when this page is updated
Intro In this video, I will show you how to use the DateAdd function in Microsoft Access to add or subtract whole calendar months from a date, such as calculating a warranty expiration date exactly six months in the future. We will look at how different intervals work with DateAdd, why adding days is not the same as adding calendar months, how to use DateAdd in queries, and how to handle common scenarios like leap years and month-end dates. This video answers a viewer question about managing warranty periods using Access.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, I'm going to show you how to use the DateAdd function to add or subtract whole calendar months to a date in Microsoft Access.
Today's question comes from Cohen from Glendale, Arizona, a silver member. Cohen asks, how do I determine what date is exactly six months in the future from a specific date? I can't just add 180 days. It's for a warranty program, so it needs to be exactly six calendar months. Furthermore, if the warranty happens to expire on the weekend, we give them until the following Monday to come in for service. How can I handle that?
Well, Cohen, we can use a function called DateAdd in Microsoft Access to add any particular interval, such as days, weeks, months, hours, minutes, and so on, to any particular date. So let's take a look and see how that works.
I'm going to begin with a blank database. Now you can get a copy of this database on my website. Look for the blank template down below.
As you can see, it's real simple. It's got a basic customer form in here. Let's say they've got a customer since field in here. So you can see a table. You can see customer since right here. Basically, I use it to determine how long they've been a customer.
Now we can use the DateAdd function to add or remove entire months, weeks, quarters, or even time intervals like hours, minutes, and seconds from any day.
Let's go over to the query and see how this works. Let's create query design. Let's bring over our customerT. Now here's customer since. Let's bring that down here. Now, I don't want to have to keep referring to this as customer since, so I'm just going to put a D in front of it like this: D: [CustomerSince]. See that? That's called an alias. Now I can just refer to this value as D in the rest of my formulas and functions.
If you want to add days to a particular date, that's simple math because Access treats days as a value of one when it comes to date/time fields. So if I say X is D + 1, that should give me one day after that customer since date.
1/1/2013, there's 1/2/2013. And you can go backwards too. I can subtract one, and that'll give me the day before.
Now, months, you can't just do whole days. So I can use a special function called DateAdd. This is going to be DateAdd and then in parentheses.
First, it wants the interval. The interval can be years, quarters, months, days, and so on, lots of different ones. So month is just inside a quote, put an "m", comma. Then how many of that interval do you want? Well, I want one month in the future. That's a 1, comma.
Then, what is the date that you want to add on to? You can put a bunch of different things in here. You could put in a field like we have D, or you could put in an actual date inside of hashtags or octothorpes, or whatever you want to call those. So I could put #1/1/2020# like that if I wanted to. Or you can use a function like today's date if you want Date() as a function. All of those are valid. But I want D, which is that field we have.
Let's see what this looks like. Let's go ahead and run the query.
Now, if I put a value in here like 9/30, I get 10/30. If I put in here 10/31, I'm getting 11/30 because November doesn't have a 31st date. If you put something like 2/29/2000 in there, you get 3/29/2000.
Here are the valid intervals for DateAdd. "yyyy" is years. Not just a single y, that's used for something else with a different function. "q" if you want to add whole quarters, "m" for months, "d" for days, although you really don't have to use DateAdd for days because you could just use basic math. There's "ww" for whole weeks. Again, for weeks, you could use plus or minus seven, but sometimes weeks makes it easier. Then you've got hours, minutes, and seconds. So you can also use it for time.
If you want to see, for example, next month, you can use similar logic. If you want to see one year from today, next year, it would be very similar: DateAdd("yyyy", 1, D). You could put in there 2 if you want two years from today. Let's do two years. This should give us exactly two years in the future: 11/2015. See, and two years in the future isn't a leap year, so it knows that.
Be careful if using DateAdd to calculate age, however. There are some other stipulations that have to deal with age. I have a separate video on calculating age. I just made it. I'll put a link down below.
If you want to calculate something like 20 minutes in the future, that'll be DateAdd("n", 20, D). Now, these should all be 20 minutes after midnight because we don't have any times on those. But let's say we got some times on some of these. Let's say this is 9:30. There's 9:50. Let's put in here 8:15 PM. It's 20 minutes after that date.
Now, Cohen wants to see a list of values that are within six calendar months of a particular day. So, instead of putting that in the field up here, we're going to use it as a criteria. Actually, we could do it as both. We could put it over here. So we could say warranty expires: DateAdd("m", 6, D). This will tell you when their warranty expires on.
Let's get rid of these times to make things easier. That's the date that the person's warranty expires. That is how you can generate a query to show you a date that is exactly six months in the future.
Now, as far as the second part of your question: if the warranty happens to expire on the weekend, we give them until the following Monday to come in for service. How can you handle that? That is going to involve a custom function and adding a little bit of VBA. I will cover that in the extended cut for the members.
Here's what we're going to cover in the extended cut for the members. We're going to build that IsWarrantyValid function using VBA because if the warranty expires on a Saturday or Sunday, then Cohen wants to move it up to a Monday. So we're going to use the Weekday function to determine that.
I'm going to show you how to make a warranty expired message pop up real big there if that person's warranty is expired when you open up their record. So if your data entry person goes to open up the record and the warranty is already expired, you'll see it right there. We'll do that in an event on the form.
How do you become a member? Click on the Join button below the video. Silver members and up will get access to all of my extended cut TechHelp videos, live video and chat sessions, and other perks. After you click the Join button, you'll see a list of all the different membership levels that are available, each with its own special perks.
But don't worry, these TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more and they'll always be free.Quiz Q1. What is the primary use of the DateAdd function in Microsoft Access?
A. To view database relationships
B. To add or subtract intervals like years, months, days, or time to a date
C. To remove duplicate records from a table
D. To export data to Excel
Q2. Why can't you simply add 180 days to find a date that is six calendar months in the future?
A. Because 180 days always lands on a weekend
B. Because six months is never exactly 180 days due to varying month lengths
C. Because Access does not support adding days
D. Because leap years add extra days
Q3. What does the "m" interval represent in the DateAdd function?
A. Minutes
B. Months
C. Milliseconds
D. Mondays
Q4. Which of the following is the correct syntax for adding one month to a date field called CustomerSince using DateAdd?
A. DateAdd("m", 1, [CustomerSince])
B. AddDate("m", [CustomerSince], 1)
C. DateSum([CustomerSince], 1, "m")
D. DateAdd(1, "m", [CustomerSince])
Q5. What is the purpose of giving [CustomerSince] an alias such as D in a query?
A. To hide the field from the results
B. To more easily reference the field in formulas and functions
C. To change the field's format
D. To apply a different data type
Q6. Which interval should you use with DateAdd if you want to add whole years to a date?
A. "yy"
B. "yyyy"
C. "y"
D. "year"
Q7. If you want to show a warranty expiration date that is exactly six months past a given date field, which of the following would be correct?
A. DateAdd("m", 6, D)
B. DateAdd("d", 180, D)
C. DateAdd("w", 26, D)
D. DateAdd("n", 6, D)
Q8. What happens if you use DateAdd to add one month to the date 10/31?
A. It always results in 11/31
B. It produces an error
C. It results in 11/30, since November has 30 days
D. It skips November and goes to December 1
Q9. Which of the following intervals is NOT valid for the DateAdd function?
A. "n" (minutes)
B. "ww" (weeks)
C. "q" (quarters)
D. "mo" (mornings)
Q10. According to the video, what is necessary to allow warranties to extend to Monday if they expire on a weekend?
A. A simple DateAdd formula is enough
B. Using the Weekday function in a custom VBA routine
C. Filtering Saturdays and Sundays manually
D. Changing the date field data type
Answers: 1-B; 2-B; 3-B; 4-A; 5-B; 6-B; 7-A; 8-C; 9-D; 10-B
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 using the DateAdd function in Microsoft Access to add or subtract whole calendar months to a date. The lesson was inspired by Cohen, who asked how to calculate a date that is exactly six months in the future for a warranty program. Cohen also wanted to make sure that if a warranty expired on a weekend, customers would be given until the following Monday to come in for service.
To solve this, I start by explaining that DateAdd is a function in Access that allows you to add a specific interval to any date. Intervals can be days, weeks, months, years, hours, minutes, or seconds. This makes DateAdd very flexible for handling all sorts of scheduling calculations.
I set up a simple database with a customer table and created a form to work with customer data, particularly focusing on a field that stores the date a customer joined. Using a query, I demonstrate how to apply DateAdd in practice. For example, if you want to add days to a date, you can use simple addition since Access stores dates as numbers where each integer represents a day. So, to get the next day, you just add one.
However, when it comes to adding months, direct addition does not work because months have different numbers of days. That's where DateAdd becomes essential. In this function, the first argument specifies the interval, such as "m" for months or "yyyy" for years. The second argument indicates how many of that interval to add, and the third is the specific date you want to modify. For Cohen's situation, adding six months would mean using "m" and the number 6.
I show different ways to specify which date to add the interval to – you can refer to a field, enter a specific date, or use a function such as Date() for today's date. The output adjusts automatically, taking things like the end of month or leap years into account. For example, if you add one month to January 31st, the function recognizes that February usually has fewer days.
I then cover all valid interval codes for DateAdd: "yyyy" for years, "q" for quarters, "m" for months, "d" for days, "ww" for weeks, and there are options for hours, minutes, and seconds as well. Although you can use DateAdd for days, simple addition is often easier for that purpose.
I also mention how to use these date calculations for different timeframes. If you want a future date a year or two from now, you simply adjust the interval and number in the function. The same logic applies for adding 20 minutes to a given time.
For the main requirement of calculating warranties, the expiration date can be determined by adding six months to the customer's starting date. This is very easy to implement in a query, giving you the exact future date the warranty should expire.
For Cohen's additional requirement – giving customers an extension to the next Monday if the calculated expiration date falls on a weekend – this logic needs a bit more work. Handling weekends requires creating a custom function using VBA. You would need to use a function like Weekday to detect whether the new date falls on Saturday or Sunday and, if so, adjust it forward to Monday.
I also mention that in the extended cut for members, I cover how to create such a custom function in VBA and how to display a clear on-screen warning if a customer's warranty has already expired when their record is opened. This adds another level of functionality for users working with warranties and customer service.
If you are interested in learning how to build the custom function or displaying a warranty expired message on your forms, these steps are included in the extended cut available to members. Details about becoming a member and the different membership levels are available on my website.
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 Using the DateAdd function in Microsoft Access
Adding or subtracting whole months to a date
Understanding intervals in DateAdd ("m" for months, "d" for days, etc.)
Using field aliases in queries for date calculations
Calculating future or past dates with DateAdd in queries
Handling edge cases like month-end and leap years with DateAdd
Calculating warranty expiration dates using DateAdd
Applying criteria in queries to filter records by date intervals
Adding hours, minutes, and seconds to datetime fields with DateAdd
Demonstrating DateAdd with various intervals in queries |
