Whole Months
By Richard Rost
14 months ago
Calculate Full Months Between Dates in Access Queries
In this Microsoft Access tutorial, I will show you how to calculate the number of whole months between two dates, a common challenge when using DateDiff. We'll address the issue where DateDiff counts partial months as complete by incorporating a technique that considers day values to ensure accuracy. You'll learn to implement this calculation in a query in Part 1. For those interested in advancing their skills, I'll demonstrate how to create a function for more streamlined usage in Part 2.
Megan from Pasadena, California (a Platinum Member) asks: How do I find out how many full months are between two dates in Microsoft Access? I tried using DateDiff, but it seems to just count the months without considering the exact days. For example, going from February 12 to March 12 gives me one month, which is fine, but February 12 to March 2 also counts as one month for some reason. I need a way to only count whole months, factoring in the days too. Could you show me how to make it work properly?
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, calculate whole months between dates, DateDiff with month setting, subtract one for whole months, function for whole months calculation, non-VBA query method, evaluate day values, IIf function in queries, mistakes with DateDiff arguments, fix DateDiff errors, ISO date format, date subtraction logic, free template demonstration, start date and end date tables, calculated query fields
Subscribe to Whole Months
Get notifications when this page is updated
Intro In this video, we will talk about how to accurately calculate the number of whole months between two dates in Microsoft Access. You'll see how the DateDiff function works and why it sometimes returns unexpected results when counting months. I'll show you how to set up a table with date fields, build a query to calculate whole months, and adjust DateDiff results so only complete months are counted by checking the day values. We'll also cover how to use the IIf and day functions in your queries to get the right answer without using VBA.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 how to calculate whole months between two dates in Microsoft Access. You want to make sure it's a whole month, not just part of a month.
Today's question comes from Megan in Pasadena, California, one of my platinum members. Megan says, "How do I find out how many full months are between two dates in Microsoft Access? I tried using DateDiff, but it seems you just count the months without considering the exact days. For example, going from February 12th to March 12th gives me one month, which is fine, but February 12th to March 2nd also counts as one month for some reason. I need a way to only count whole months factoring in the days too. Could you show me how to make it work properly?"
Yes, Megan, you have discovered exactly how DateDiff works. DateDiff, if you use the month setting with the "M," will only look at the month. So it doesn't matter if it's February 28th to March 1st, that's one month.
Let's take a look. This is what we're going to build in just a minute. Take a start date, and then DateDiff. If you got 1-1 to 2-1, that's one month, that's fine. That's a whole month. But January 5th to February 1st, that's less than a month. It should be up to February 5th, but DateDiff will give you one. Our whole months calculation will figure that's zero. Same thing with January to March. January 15th to March 1st, even January 31st to February 1st shows up as a whole month.
The key here is we have to use DateDiff. We're still going to use DateDiff, but then we're going to look at the day value. And if the day value of this guy is less than the day value of that guy, then we've got to subtract one from the final calculation. And how do we do that? Well, I'm going to show you two different ways, and that's why I've got expert and developer up here.
First, I'm going to show you how to do it just in a query, or you could do it in a form field if you want, or a report. And then for the smarty-pants kids in class, I'm going to show you how to make a function to do it, which I think is actually easier.
Let's take a look at the non-VBA way first since I know a lot of you are scared of VBA. Before we get into it, there are a couple prerequisites I want you to watch. If you don't know how to use DateDiff, watch this first. Then we're going to use the if function, immediate if, so we can figure out if that day is less than the other day. So watch this video. You'll need to know how to use the year, month, and day functions. We're actually going to just use the day function to determine the day of a date, so January 1st to get the 1, for example. Either way, January 2nd, you'll get a 2. And you'll need to know how to make a calculated query field. These are all free videos. They're on my YouTube channel. They're on my website. Go watch these and come on back.
Here I am in my TechHelp free template. This is a free database. You can grab a copy off my website if you want to. Let's make a table real quick, just a simple table. It's got a couple dates in it. So, a DateID because I like to have an auto-number in each of my tables. We'll have a StartDate and an EndDate, and those are date-time values. We'll save that as my DateT table. Primary key, sure, and let's put a few dates in here.
Let's do the same ones I had before. We'll go with 1-1, and I use the ISO date standard which is year-month-day. If you don't know what that is, I have a whole video on it. I love preaching the gospel of the ISO date format. Go watch this if you're curious. We'll go to 2-1 on this one. Let's do 1-5 to 2-1 which is less than a month. Let's do 1-1 to 3-1. It should give us two months. 1-15 to 2-1, that'll give us less than a month again. Then we'll go 1-31 to 2-1. Then we'll go 1-1 to 1-2 and see if we get there. Got a whole bunch of a variety of different dates.
Close that up. Now, let's make a query so we can do some work in there with the DateDiff function. I'm going to bring in my DateT. Let me close that guy. I'm going to bring in the star so all the fields are in here. Then right down here, I'll put my DateDiff function. I'm going to zoom in so you can see it better. Shift F2 opens up my zoom window. My zoom window's too big. There we go. So let's make a new field called dd, and we're going to say it'll be DateDiff, months with the "M," and then StartDate, EndDate. StartDate, EndDate. I'll hit okay.
Let's save this query real quick as my DateQ, and let's give her a run. There you can see all the mostly wrong answers. It's right as far as what DateDiff cares about. DateDiff with the month just cares about the month. Is this less than this as far as months go? Yeah, that's one whole month. How about this one? January to January is a zero, which is correct. How about this one? January 31st to February 1st is not a whole month. So now we have to look at and say, is this value here, the end day, the day of the end, the day of the month, less than this value? If so, we've got to subtract one from that.
Back into design view, and I'm going to copy this guy. I'm going to leave this guy alone. We're going to copy that one and come over here and make a new one. Shift F2 to zoom in. I'm going to paste that in there. We're going to change this. We're going to call this the WholeMonths field. We're going to start off with DateDiff, that's fine, leave it alone. Now, we're going to say if the day of the EndDate is less than the day of the StartDate, then we're going to subtract one. Otherwise, leave it alone. In this case, we're going to subtract the result of the if function. If Day of EndDate is less than the day of the StartDate, put a one here. Otherwise, put a zero there.
It's going to evaluate this first. Is the day of the EndDate less than the day of the StartDate? Let's say it is, so that'll be a one. This if function, this whole thing, will evaluate to a one. So this will be DateDiff, whatever that is, subtract one. Otherwise, it'll evaluate to a zero, subtract zero. The way that these functions are written, it's kind of a problem I have with Excel too sometimes. You have to think about it, whereas VBA code, as you'll see in a few minutes, actually works, it's actually easier to read and write, I think.
Let's hit okay, save it, and run. Wrong number of arguments. What did I do? Come back in here. Let's take a peek. That's interesting. I just made a goof, and I didn't catch it. Did any of you catch it? It's saying that I entered a formula with the wrong number of arguments. Do you see what it is? I like leaving mistakes like this in the video because if I make this mistake, I'm sure you will eventually, and I have gotten this exact question before. Take a look at it and see if you can figure it out. Anybody see it? Pause the video if you need to.
The problem was I used "date" here instead of "day," like I did over here. "Date" is a function that returns the current date based on the system calendar, and it doesn't take any parameters. That's why it said an invalid number of arguments. It's expecting nothing in here. So that was my bad. This should be "day" of the StartDate. Typical mistake. I've done this before, by the way, but I want to leave that in there so you see what it is.
Let's save it, give it a run, and there we go. Those are correct numbers. Now you got whole months. This day is less than that day, so it subtracted one from this value there, and everything is working fine. Like I said a second ago, this isn't exactly - I don't want to have to do this everywhere that I want this calculation. It'd be nice if I just had a simple function; then I could say, "Hey, give me whole months." Well, guess what? We can do that if we know a little VBA, and I will teach you how to do that in tomorrow's video. Tune in tomorrow, same bat time, same bat channel. Or if you're a member, you can watch it right now because that's one of the benefits of membership. You don't have to wait. You can just watch them as soon as they're posted.
That's going to do it for today, folks. I hope you learned something. Live long and prosper, my friends. I'll see you tomorrow for part two.
TOPICS:
Calculate whole months between two dates
Understanding DateDiff functionality
Using DateDiff for month calculations
Adjusting DateDiff outcomes with day values
Creating a query in Microsoft Access
DateDiff function in Access queries
Using ISO date format in Access
Subtracting from DateDiff results conditionally
Using the IIf function in queries
Building tables with date fields in Access
Creating calculated fields in Access queries
COMMERCIAL:
In today's TechHelp video, we will discuss how to accurately calculate whole months between two dates in Microsoft Access. If you've encountered issues with DateDiff counting incomplete months as full ones, we've got a solution for you. First, we'll learn how to achieve this using a non-VBA method in a query. We'll use DateDiff and then apply an if function to subtract one month when the end date's day value is less than the start date's day. You'll also learn how to correct common mistakes, like mixing up 'date' and 'day.' Finally, if you're curious about a VBA solution for creating a reusable function, you'll want to tune in to our next video. This lesson is packed with practical tips to enhance your Access skills. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper, my friends.Quiz Q1. What problem does Megan encounter when using the DateDiff function to calculate months between dates?
A. The DateDiff function cannot calculate months at all.
B. The DateDiff function includes times, not just dates.
C. The DateDiff function counts full months without considering the exact days between dates.
D. The DateDiff function shows wrong years, not months.
Q2. Which setting does the DateDiff function use to only look at the month?
A. It uses the "D" setting.
B. It uses the "M" setting.
C. It uses the "Y" setting.
D. It uses the "W" setting.
Q3. In the context of the video, what needs to happen if the day of the EndDate is less than the day of the StartDate?
A. Add one to the final calculation.
B. Multiply the result by two.
C. Subtract one from the final calculation.
D. Divide the result by two.
Q4. In the example given, which two dates demonstrate a situation where DateDiff incorrectly counts one month due to day values?
A. January 1st to March 1st
B. February 28th to March 1st
C. January 5th to February 5th
D. January 31st to February 1st
Q5. Why is using the VBA function preferred over the non-VBA approach according to the video?
A. It doesn't require any coding knowledge.
B. It is more accurate but requires more steps.
C. It is easier to read, write, and can be reused across various calculations.
D. It performs calculations faster than the built-in functions.
Q6. What mistake did the instructor make when entering the function in the query, leading to an error?
A. The use of incorrect field names.
B. Using the "date" function instead of "day" function.
C. Forgetting to include the end date in the function.
D. Using the wrong calculation formula.
Q7. What is one of the benefits mentioned about being a member on the instructor's website?
A. Members get personal coding support 24/7.
B. Members receive a physical copy of the tutorial scripts.
C. Members can watch videos immediately as they are posted.
D. Members get a discount on Microsoft Access software.
Q8. What is the instructor's suggested method for calculating whole months when scared of using VBA?
A. Use a pivot table in Access.
B. Use a non-VBA query with conditional logic.
C. Use Excel to handle the calculations instead.
D. Utilize SQL scripts to compute the result.
Answers: 1-C; 2-B; 3-C; 4-D; 5-C; 6-B; 7-C; 8-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 TechHelp tutorial from Access Learning Zone covers the process of calculating whole months between two dates using Microsoft Access. As your instructor, Richard Rost, I'll guide you through the steps to ensure that you're counting complete months, not partial ones.
The motivation for this session comes from a question posed by Megan from Pasadena, California, a platinum member of our community. Megan wanted to determine how many full months span between two dates in Access. She noted that the DateDiff function, which she initially tried, counts the months without considering specific days. For instance, February 12th to March 12th correctly registers as one month, but February 12th to March 2nd also shows as one month, which isn't what she needed. Megan's goal was to count only complete months, taking the days into account.
As Megan discovered, DateDiff with the "M" parameter only focuses on the month portion, ignoring the exact day. For example, it treats February 28th to March 1st as one month. For a more precise count, we'll use DateDiff combined with a method to evaluate the day values of the start and end dates, adjusting the count when necessary.
Let's start by tackling this via a query method, as it's less daunting for those who might be uncomfortable with VBA. First, it's important to be familiar with DateDiff, along with functions like Year, Month, and Day. These will be necessary, especially the Day function, to interpret the specific day from a date like January 1st.
I'm working within the TechHelp free template, accessible from my website. We'll begin by creating a simple table with auto-numbering, including StartDate and EndDate as date-time fields, and save it as DateT. We'll then input sample dates similar to those previously mentioned.
The next step involves crafting a query to utilize the DateDiff function. We'll include all table fields and apply the DateDiff function, using "M" for months, on the StartDate and EndDate. The initial outcome will show months based on DateDiff's general logic, which focuses solely on the transition between months, not complete day spans.
To refine this and account for day differences, we'll enhance the DateDiff calculation. By implementing an "if" condition, we can adjust the month count when the day value of the end date is less than the start date's day value. We'll introduce a new field, WholeMonths, and modify the DateDiff outcome accordingly. If the day of EndDate is less than that of StartDate, we subtract one month from the count; otherwise, we leave it as is.
After correcting a common mistake with functions (the use of "date" instead of "day"), the final query will accurately reflect whole months between two dates. This query offers a straightforward approach without delving into VBA.
For those keen on an even more streamlined solution, we can develop a custom VBA function to perform this calculation, which I'll explain in a follow-up video. This allows seamless integration across various forms and reports. Membership offers the benefit of immediate access to such content without waiting.
That's all for today's lesson. Stay curious and keep expanding your skills. For a complete video with step-by-step guidance on everything discussed, visit my website at the link below. Live long and prosper, my friends.Topic List Calculate whole months between two dates
Understanding DateDiff functionality
Using DateDiff for month calculations
Adjusting DateDiff outcomes with day values
Creating a query in Microsoft Access
DateDiff function in Access queries
Using ISO date format in Access
Subtracting from DateDiff results conditionally
Using the IIf function in queries
Building tables with date fields in Access
Creating calculated fields in Access queries |
