Next Payment Date
By Richard Rost
2 years ago
Calculate Next Payment, Reporting Dates
In this Microsoft Access tutorial, I'll guide you through the process of calculating the next payment date based on a fixed day of the month, such as the 7th day. Whether it's January 1st or January 18th, you'll learn how to determine that the next payment will always be on the following 7th of the month. This valuable skill will enable you to efficiently calculate future payment dates, providing clarity and control over your payment schedule.
Fiona from Duluth, Georgia (a Platinum Member) asks: I am building a database to track my credit cards. I like to leave a little balance on each of my cards on the date that they are reported to the credit bureau so that they see activity and not just a zero balance every month. If I know that the card is reported on, say, the 20th of the month, how can I display that to show the next reporting date? So if it's currently December 30th, I want it to show January 20th. This would also be helpful for finding your next payment date, so if your bill is always due on the 17th of the month and today is the 13th, you know you have 4 days left. If today is the 20th, you know you've got until next month. I'm using Excel for this now, but it would be nice to have it in my database.
Members
Members will learn how to rewrite the function so it can deal with NULL values. If you have a payment in your table that doesn't have a specific day of the month it's paid on, so you leave that field blank, the function will return an #Error - unless you do what I show in this video.
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
Links
Recommended Courses
Keywords
TechHelp Access, payment same day of month, credit card payment tracking, credit bureau report date, calculate next reporting date, future payment date calculation, Access date functions, calculate due dates, automatic date calculation, credit card payment scheduling, loan payment tracking, Access billing cycles, calculate payment deadlines, date calculation logic, next payment date algorithm, credit card reporting date formula, automate credit card tracking, Access payment date function, due date calculation method.
Intro In this video, I will show you how to calculate the next payment date for recurring bills or events that are due on a specific day each month in Microsoft Access. We will look at both a VBA function and a query method for determining the next due date based on today's date, using a table that tracks due days for various bills. You'll see how to handle cases where the due date has already passed for the current month and how to use functions like DateSerial, Year, Month, and Day to build your solution.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost.
In today's video, I'm going to show you how to calculate the next payment date for something that you know is due on a specific day every month. Whether it's a credit card payment, your electric bill, or the day you get paid, if it's a bill or something that happens on a specific day of the month, like the 28th or the 15th, based on today's date, I'll show you how to calculate the next one.
Today's question comes from Fiona in Duluth, Georgia, one of my Platinum members. Fiona says: "I'm building a database to track my credit cards. I like to leave a little balance on each of my cards on the date that they are reported to the credit bureau so that they see activity and not just a zero balance every month. If I know that the card is reported on, say, the 20th of the month, how can I display that to show the next reporting date? So if it's currently December 30th, I want it to show January 20th. This would also be helpful for finding your next payment date. So if your bill is always due on the 17th of the month and today is the 13th, you know you have four days left. If today is the 20th, you know you've got until next month. I'm using Excel for now, but it would be nice to have this in my database."
Yes, absolutely, Fiona. We can calculate this with either a query or a little piece of VBA code - we can make our own function. That's why I tagged this as both an expert and a developer class because, for me personally, I've been programming since I was a kid, so I just think in VB code. I think it's easier for something like this to write the VBA code first, and then if you need it in a query or directly in a form, backtrack and put it into a query format. I'm going to show you both.
We're going to start with the VB code, and if you still don't quite get what this means, here's an example right here. Let's say today is the 21st of December - ISO dates of course. If your electric bill is always due on the 24th of the month, your next date is going to be December 24th because today is the 21st. Just like the phone bill that's due on the 28th of the month. You have seven days, you've got until the 28th. But if your mortgage is due on the first, then you have to upgrade that to January 1st - move it ahead. What's the next first day of the month? What's the next 15th day of the month? That's next month. This is what we're going to calculate in today's video.
Before we get started, let's talk about some prerequisites. If you haven't watched any of this stuff, it's on my YouTube channel, it's on my website, it's free. Go watch this first.
First up is my Year, Month, Day video. It teaches you how to break up a date into its constituent parts - year, month, day.
Next up is DateSerial. DateSerial goes the opposite direction; if you have the constituent parts, you can build a valid date field. So if you've got the year, the month, and the day, you can put those together to make a date. We're going to go both ways in this video.
If you want to learn how to do this in a query or a form or a report directly, learn the IF function. It's called the Immediate If function. It's basically an IF...THEN statement inside of a function.
If you want to learn the VBA method, which I personally prefer, then go watch my Intro to VBA class if you've never done any VBA before. It's about 20 minutes long. Don't be scared, VBA is really easy to learn, especially when I'm teaching it. Then go watch my IF THEN video to teach you how IF THEN statements work, which is the same thing as the IF function, just written differently like this. Also, go watch my Create a Function video where I teach you how to make your own functions so you can send it some stuff, it'll do something to it, and send you back a value.
If you're curious what this ISO date format that I use is, go watch this video. I'm on a mission to get everyone to switch to ISO dates everywhere. These are all free videos. They're on my YouTube channel, they're on my website. Go watch them and come on back.
Let's say we've got a little table. Let's create a little table real quick. So: Create - Table Design.
I've got my Bill ID, and again, this works with credit cards or bills or whatever, as long as it's something that you know is paid on the same date every month (the 13th or the 18th or whatever). That'll be our AutoNumber. We'll call it the Bill Name.
I'm going to call it the Due Day, not the Due Date - the day of the month, and this will be a Number of type Long Integer. If you want to put a validation rule down here, this has to be greater than or equal to one and less than or equal to 31, just like that.
I'll save this as my BillT.
Let's put some sample data in here. We've got the electric, which is due on the 15th. We've got the gas, which is due on the 3rd. What's today's date? Today's actually December 22nd. Let's do some stuff in the future here. Let's do the car payment, which is due on the 25th, and the mortgage, which is due on the first. And one more: let's do your $5.99 CD membership, which is due on the 31st. Let's call it the TechHelp Gold Membership.
Now, like I said, I think in VB code, so I'm going to write this first as a VB function. Those of you who are not VB programmers, just follow along and let it go in one ear and out the other if you want to. I want you to just be exposed to it to see how easy this stuff is. When I'm done writing this, we're going to backward write it into a query.
I'm just showing you how my brain thinks. Let's go down to our global module, which if you don't have one, add one.
We're going to write a public function and we're going to call this NextMonthlyDate. I'm going to send into it a day of the month as a Long, and it's going to return a value as Date.
First thing I'm going to do is say - you're sending me a number like 15. Is that number greater than or equal to the day value of today's date? So, if DayOfMonth is greater than or equal to whatever today's date is (Day(Date)), for December 22nd, this is going to be a 22. Is the DayOfMonth I send into it greater than or equal to that number? If that's the case, put some comments in here: if the day is today or after, it's going to be this month.
If the day you send in is today or after, and you can change this by the way - if you want it to be only in the future, just get rid of the equal sign. So, if you're assuming today's date is already gone - if it's the 22nd and you send a 22 and you want next month, just get rid of that equal sign. But I'm saying if it's today's date, show me today.
So in this case, put together a date this month with whatever day it is that you send into it.
So, NextMonthlyDate =
DateSerial(Year(Date), Month(Date), DayOfMonth).
This is where we build the date value back up. Today is 2023-12-22. If I send in a 28, 28 is greater than or equal to 22, so we're going to get 2023-12-28 when you send it in.
If that's not the case, we've got to go plus a month.
Else: Otherwise, it's going to be next month. So I'm just going to copy this one up here, and then we're going to add a month to it. Where do we add the month? Right there. Make sure you don't put it in here because that's going to add one to the date first and then find the month of it. You want to put it there.
And that's the end of your function.
Now you're probably thinking, "What happens if the day you send in is like the 31st and it happens to fall on January and next month's February? There is no February 31st." Well, that's one of the nice things about DateSerial. DateSerial automatically takes that into consideration. We'll see some examples in just a minute.
Let's give it a shot. Let's save it. Yes. Let's test it.
Come out and make a query: Create - Query Design. We can bring in our BillT, and then close all this stuff up. Bring in all these guys and now we'll use our function. Our function name is NextMonthlyDate, so I can use that function - that's the beauty of writing functions. You can use them in your queries, in your forms, and everywhere in your database.
So let's go: ND (for Next Date) is NextMonthlyDate(DueDay) - we're going to send it, and then we'll run it, and there's our data.
Today is the 22nd of December. So a 15 gives you next month, a 3 gives you next month, a 25 should give you this month. The first and the 34 - that is working just fine.
Let's save this as MyBillQ.
If you want to test it with February, we can cheat. We can go into our function here. We can set a target date in here. So we could say: Dim t as Date. t = (instead of saying Date - we'll put a target date in here). Let's say today is actually January 22nd, 2023.
I know I'm using ISO dates, but in VB it still likes this format. I've got a beef with Microsoft over this - I'm working on it.
Now, everywhere you see Date down here just replace it with t. So now this function will think that today is January 22nd, 2023, and we can test our code with a different date.
You might have to rerun the query. That looks good. Now let's test it for February. January (if today is the 22nd) does have a 31st, so let's test it now: let's say today is February 22nd. Let's see what happens.
There you go. See, a 31st got pushed to March. The first worked fine - February 1st went to March 1st just fine. If today is February 22nd, it's going to March 1st. February 25th? February 31st doesn't exist, so it moved ahead to March 3rd.
Now, again, this is completely dependent upon how your credit card company, your bank, whatever, your mortgage company, how they calculate. They might say that if your due date is the 31st and it's fallen in a February, you've got to pay it on the 28th, so you check with them first. But I have found most places that have due dates try not to make them on the 29th, 30th or 31st for just this reason. Due dates are usually 28th and before, at least all mine have been for my entire life. If not, I call and switch it, so that's up to you.
The code is good. I am going to go now and switch this back to today's Date, and I'll leave this like this in the code, so you can set a target date if you want to for testing purposes.
Now I promised you I'd show you how to write this in query format using the IF function, and we'll just do that down here.
We'll just call it x for now, so it's going to look like this: x: IIF(condition, value if true, value if false)
Our condition is the line we used above, the value if true is like our DateSerial for this month, and the value if false is like our DateSerial for next month. That would be your query function right there, and you can see how that's not quite as easy to understand or to read or to write as the function is. I think VBA is much more intuitive than trying to do this in a query.
Now that we have this, if you want to put that directly in a query you can. Let's just stick it in here in design view. I'll go to the next field over, I'll say x: and then put that thing in there. Of course we don't have a t, so you have to replace all these t's with Date. All those should get replaced with the Date function.
Let's give it a run - oh, DayOfMonth, hang on. DayOfMonth - that's my DueDay field since we sent that into the function as a parameter, so I have to replace DayOfMonth with DueDay. That's my bad. I'll put that here, here, and there. That should do it.
Run it now. No - Date, oh Date - one more thing. It needs to be Date(), because it's a function and queries don't often get that. So we have to replace this with Date().
I should have caught that originally. Sometimes it lets you get away with it when you type it in the first time, sometimes you get it.
Now let's try - third time's the charm! There we go. You should get the same results. This column should be the same as that.
Again, as you can see, this is a whole lot less intuitive. I think the code is easier to read, easier to write, and this is why I preach learning VBA code.
And how do you learn VBA code? Well, you take my Developer courses, by the way. You start from Developer 1 and you work your way up and learn how to program in VBA and you'll be an awesome expert just like me.
If you want to learn more about dates and times, I've got a couple of courses: specifically Access Expert Level 27 and 28, that you might find interesting. This is my comprehensive guide to Access functions, where I go through all the functions - date/time functions, string functions, logical functions, trigonometric functions, all the functions. Expert 27 is date/time part one, and then there's expert level 28, which is date/time part two. We go through all the different date functions in order and I teach you all kinds of cool things about them.
Also, if you literally just want to learn about dates and times, I've got this thing called the Access DateTime Seminar where we go through all this different cool stuff - calculating the network days (Excel has a NetworkDays function but Access doesn't), how to create a holiday exclusion table (if you want to figure out the number of work days between December 20th and January 15th but you also want to give people off for Christmas and New Year's, I'll show you how to figure that out). All kinds of stuff in the seminar.
A lot of people say to me - I get a lot of emails like, "Why should I buy your courses when you just teach everything on YouTube with these TechHelp videos?" Well, I do, and I love doing it. I love giving that knowledge away for free on YouTube and teaching people stuff. But in my TechHelp videos, I just give you bits and pieces. We cover a specific topic and tackle that problem. I answer that question.
My courses are set up to be a lot more comprehensive, so I teach you from beginning to end how to logically progress from this to that, whereas I don't have the time to do that in the TechHelp videos. The TechHelp videos are just: "Here's how we solve that problem - there you go." My Developer courses are designed to be one after the other, how you learn in school. That's how I design my lessons. So check it out if you're interested in learning more.
Back to our query here. This works great, and it's beautiful. But what if you've got some bills in here that don't have a specific due date and you end up with a null? This function and this query can't handle null values.
So, how do we handle null values? We will cover that in the extended cut for the members. It's not just wrapping it in an IsNull function; that's one way you could do it with the query version. It makes your query even longer and crazier. I'm going to show you how to handle it in the function itself.
That's going to be in the extended cut for the members. Silver members and up get access to all of my extended cut videos, not just this one - all of them. Gold members can download these databases that I build in the TechHelp videos, and everybody gets free classes. You get a free class and you get a free class - all members Silver level and up get a free class every month. Check it out. You'll find links down below.
That is 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.Quiz Q1. What is the primary goal of the video tutorial?
A. To demonstrate how to export data from Access to Excel
B. To calculate the next monthly payment or reporting date based on a given day of the month
C. To explain how to join tables in Access
D. To set up automatic email reminders in Access
Q2. What function does Richard suggest is most intuitive for handling this calculation?
A. SQL query with multiple joins
B. A custom VBA function
C. An Excel macro
D. The DLookup function
Q3. Which Access function is used to construct a date from individual components (year, month, day)?
A. DatePart
B. DateDiff
C. DateSerial
D. DateValue
Q4. According to the video, if today is December 22 and a bill is due on the 25th, what is the next due date?
A. December 25 of the previous year
B. December 25 of the current year
C. January 25 of the upcoming year
D. December 22 of the current year
Q5. What happens if the specified due day is less than today's day of the month?
A. The function marks the payment as overdue
B. The function returns the same date as today
C. The function calculates the due date for the next month
D. The function returns an error
Q6. How does the DateSerial function handle cases where the due day does not exist in the next month (for example, the 31st in February)?
A. It throws an error and stops execution
B. It automatically adjusts and moves the date into the next valid month
C. It sets the due date to the last day of February
D. It returns a null value
Q7. In Richard's example, what is stored in the "DueDay" field in his BillT table?
A. The full due date including year, month, and day
B. The day of the month when the bill is due (numeric, e.g., 15)
C. The total amount due for the bill
D. The last payment date
Q8. When rewriting the VBA logic into a query, what built-in function does Richard use to handle conditional logic in Access queries?
A. IF...THEN...ELSE statement
B. Choose function
C. IIF function
D. Swtich function
Q9. What does Richard say about handling null values in this calculation?
A. The function and query can handle nulls with no modification
B. Nulls are ignored by default in all Access calculations
C. Null handling requires extra logic and is covered in the extended video for members
D. Null values are automatically set to zero
Q10. Why does Richard prefer writing a VBA function for this solution?
A. VBA functions cannot be reused elsewhere
B. He finds VBA code easier to write, read, and reuse across forms, queries, and reports
C. Query syntax is more intuitive than VBA code
D. VBA is required for every calculation in Access
Answers: 1-B; 2-B; 3-C; 4-B; 5-C; 6-B; 7-B; 8-C; 9-C; 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 TechHelp tutorial from Access Learning Zone focuses on calculating the next payment date for recurring monthly bills in Microsoft Access, such as credit card payments, utility bills, or any other obligation that is due on a specific day each month.
The example question comes from someone who wants to keep track of when their credit cards are reported to the credit bureau. The goal is to always know the next reporting date based on the current date and the fixed day of the month that applies to each card or bill. For example, if today is December 30th and the reporting date is always the 20th, the next reporting date should be January 20th. Similarly, if a bill is due on the 17th and today is the 13th, you have four days left; if today is the 20th, the next due date is the 17th of the following month.
This can be accomplished using either a query or some VBA code in Access. Personally, I prefer the VBA function approach because of its flexibility. Often, I find it easier to solve the problem with code first, and then adapt the logic for use in a query or form if necessary. I will explain both methods so you can choose what works best for you.
Before we dive into the details, there are some helpful prerequisite videos you should watch if you're not familiar with certain concepts. My "Year, Month, Day" video explains how to separate a date into its individual year, month, and day components. The "DateSerial" video covers how to construct a date from those parts. I also recommend learning about the Immediate If ("IIF") function, which is essential for conditional logic in queries, and, if you are interested in VBA, check out my introductory VBA classes, my video on IF...THEN logic, and how to create your own functions. These resources are available for free on my website and YouTube channel.
To demonstrate the process, I created a sample "Bills" table in Access. Each record has an ID, a bill name, and a "Due Day" that indicates the day of the month the payment is due (such as the 1st, 15th, or 28th). The "Due Day" field should be a long integer between 1 and 31.
Next, to determine the next occurrence of the payment date, I start with a custom VBA function. Essentially, this function takes a "due day" as input and compares it to today's day of the month. If the due day is today or later in the current month, the function returns the date occurring this month. If the due day has already passed, it calculates the corresponding date in the following month. I use the DateSerial function to avoid issues when the day doesn't exist (for instance, if the due day is the 31st and the next month is February), since DateSerial automatically rolls extra days into the next month as needed.
After writing and testing the VBA function, I run a query that applies the new function to each record in my Bills table. This way, I can instantly see the next due date for all my bills, regardless of when they fall in the month.
For those who prefer not to use VBA, I show how to write the same logic using the Immediate If (IIF) function inside a query, although I find it less readable and harder to maintain. Essentially, the condition checks whether the due day is today or later. If so, it creates a date in the current month; otherwise, it advances to the next month.
If you want to test your logic as if today were a different date (e.g., in February), you can introduce a target date variable in the function to simulate how the code would behave. This is useful for testing edge cases, such as dealing with months of different lengths.
It's important to note that some companies may not process a due date that falls on a non-existent day of the month, like February 30 or 31. Most banks and service providers avoid this by choosing due dates that always exist, but you should double-check with your provider to confirm.
If you run into issues with missing "due day" values (nulls) in your data, the current versions of the function and query will not handle them gracefully. In the extended cut of this lesson, I demonstrate how to better manage null values directly within the function, going beyond just wrapping the query in an IsNull function. This approach allows you to handle missing data more elegantly.
For those looking to expand their knowledge, I recommend my Developer courses, which teach VBA and database development systematically, starting with the basics and building up your skills. If you want a deeper understanding of dates and times in Access, check out Expert Levels 27 and 28, which cover date and time functions in detail, or take a look at my Access DateTime Seminar for advanced techniques.
To sum up, with the approach shown here, you can easily track due dates for any bill or recurring payment and always know when the next payment is coming up. This is especially useful for managing finances and ensuring nothing slips through the cracks.
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 Calculating the next monthly due date based on a given day
Creating a bills table with a due day field
Validating the due day field for valid day numbers
Writing a VBA function to determine the next due date
Handling months with fewer days using DateSerial in VBA
Testing the due date calculation across different months
Using a parameter to set a custom "today's date" for testing
Implementing the function in a query to display next due dates
Translating the VBA logic into an Access query using IIF
Adjusting query expressions for correct field and syntax usage
Comparing VBA and query approaches for due date calculation |
