Access EOMONTH
By Richard Rost
3 years ago
Excel's EOMONTH Function in Microsoft Access
In this Microsoft Access tutorial, I'm going to teach you how to create a replica of Excel's EOMONTH function using VBA. You'll learn how to write a custom function that will allow you to find the end-of-month date based on a given date and the number of months you want to add or subtract. This will offer you a seamless way to extend Excel's capabilities right into your Access databases, giving you more control and flexibility over your date calculations. Stay tuned to discover how to bring this useful Excel feature into your Access toolkit.
Ethan from San Marcos, Texas (a Platinum Member) asks: I'm in the process of transitioning numerous Excel spreadsheets to a Microsoft Access database. One function that we frequently use in our spreadsheets is EOMONTH. Is there a way to replicate this functionality within Microsoft Access?
Prerequisites
Code Vault
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, end of month, last day of month, last day of next month, last day of previous month, Excel EOMONTH Function in Access, Last Day of Month, EOMONTH in Access, DateSerial, End-of-Month Calculations, Access Date Functions, EOMONTH Function, Transitioning to Access, Excel Functions in Access, Date Manipulation in Access, Replicate Excel in Access
Subscribe to Access EOMONTH
Get notifications when this page is updated
Intro In this video, I will show you how to recreate Excel's EOMONTH (End of Month) function in Microsoft Access using VBA. You'll learn how to build a custom EOMONTH function in Access that lets you calculate the last day of a given month, with the ability to add or subtract months as needed. We'll look at how the original Excel function works, go through creating a public VBA function, and test it in the Immediate window so you can use it in your queries, forms, and reports.Transcript Today we're going to recreate Excel's EOMONTH function - that stands for End of Month - in Microsoft Access VBA. Today's question comes from Ethan in San Marcos, Texas, one of my platinum members.
Ethan says, I'm in the process of transitioning numerous Excel spreadsheets to a Microsoft Access database. Good for you. One function that we frequently use in our spreadsheets is EOMONTH. Is there a way to replicate this functionality within Microsoft Access?
Well of course, Ethan, this isn't very hard to do in Access. Let me show you how you can make a custom function in VBA so that you can just call it EOMONTH wherever you want to, in your forms and reports and your queries and whatever.
Now, first I want you to go watch my first day of the month video. In this video, we use the DateSerial function and a few others to find the first day of the month and the last day of the month. We do this in a query with no VBA, but one thing that EOMONTH adds is the ability to add a number of months to it so you can say find the last day of next month or three months from now or twelve months from now, whatever. We're going to add that little bit of functionality in the function we are going to create. But definitely go watch this video first.
And of course, today this will be a developer video. What does that mean? Well, that means we are going to be using some VBA. If you do not know how to program in VBA, don't worry - it's not hard. Go watch this video first. It's my intro to VBA. It's about 20 minutes long and it will teach you everything you need to know to get started.
And we are going to create our own custom function today. We're going to make an EOMONTH function we can use in Access. So if you have never done that before, go watch this video on how to create your own custom function. It will make today's lesson a whole lot easier.
These are all free videos. They're on my website. They're on my YouTube channel. Go watch them and then come on back. I'll wait for you.
First, let's take a quick look at how EOMONTH works in Excel if you're not familiar with it. So let's say you've got a date. There's today's date. And let's say you want to figure out what the last day of that month is.
We can use the EOMONTH. Equals EOMONTH. It returns the last day of the month before a specified number of months. So we'll go with this as the start date. Now if you don't want to add any months to it, just put a zero here. In other words, don't add another month. Enter. It formats it as a number, so we just have to use the format painter to format that as a date. And there you go. That's the last day of this month.
Let's put that zero up here. And we'll go one, two, like that. And I will autofill and slide all this stuff across. Then we'll take this guy and reference that zero by using the cell reference instead. And then of course, we have to make this an absolute reference. So we'll click here and go F4. Enter. That way this doesn't slide across. It keeps it on that date. Now we'll bring this across and then we'll widen all this stuff out.
If you don't know how to do any of that stuff I just did, go watch my Excel videos. They're really good too. Access is my specialty, but I know a little bit about Excel as well.
The point I'm trying to make is this is zero months in the future. What's the end of the month date? Here's one month in the future. So October end of month, November end of month, and so on. We want to replicate this functionality in Access with a function that is also called EOMONTH. We'll send to it a date and then how many months in the future.
Here I am in my TechHelp free template. This is a free database. You can grab a copy from my website if you want to. In here I have a global module. This is where you want to put the functions you create so they're public and they can be used anywhere in the database. So let's open up that global module.
If you don't have a global module in your database, that's okay. Let me get rid of this for a second. All you have to do is go over to Create and then pick Module. Don't pick Class Module. Pick Module.
Back here in my global module, there's a couple things in here. Don't worry about the stuff like the sleep function and some other stuff. We're going to come down to the bottom. We're going to create a public function.
Public means everybody can use it. Function means it's going to return a value. We're going to call it EOMONTH, just like in Excel. If you want to make it EOmonth like that, that's fine. I'm going to make it all caps because that's an Excel function. In Excel, all the functions are in caps. Is that important? No, absolutely not.
EOMONTH takes two bits of data: D as a Date – that's the starting date you give it – and then a number of months to add. Now a lot of the times when you're using EOMONTH, me personally, I just want the end of the current month. I'm going to make this optional, a little bit different from Excel.
So we'll say Optional and then MonthsToAdd as a Long and the optional value will be zero. If I don't give it a value, it will just use the current month. As Date means this function is going to return a date value.
In the first day of the month video, we learned how to find the first day of the month and the last day of the month. This one is going to be a little bit different because we have to simply add in there the number of months to add.
So EOMONTH, the value of the function (this is what it's going to return), equals DateSerial(Year(D), Month(D) + MonthsToAdd + 1, 0). Now here's where it gets tricky. We have to add MonthsToAdd. Because DateSerial works a little bit differently than EOMONTH in Excel, you have to add one more to it.
If you put a zero here for the day, it goes minus a day. If you put one in there, it would give you the first day of the month. A zero means it goes back a day. It's a little trick with DateSerial. I think I talked about this in the DateSerial video. That zero says go back a day. So if I put in here like 2000, 1, 1, you'd get the last day December 31 of 1999. That's how that works.
And that's it. That's all you need right there. It should replicate EOMONTH in Excel. Save it.
Let's throw in a quick Debug Compile. If you want to test it without making a query or a form or whatever, let's open up the Immediate window. View and then Immediate Window. Right down here, we can type in commands like ?EOMONTH. Let's put today's date in here. So inside of the hash signs, pound signs, hashtags, whatever you want to call them, octothorpes: #2023-09-18#. Let's just do without a comma first. Enter. There you go. Here's the end of this month.
Want to do next month? Let's go back: 1. Enter. There you go. Let's try going back a month: -1. Enter. There's 8/31. Sweet.
Now you can use EOMONTH in your queries, in your forms, in your reports, wherever you want to use them.
If you like this stuff and want to learn more about working with dates and times and all kinds of things with moving parts and molecular structures and stuff, my Access Expert Level 27 is part one of my date and time functions lessons. I cover everything you want to know about dates and times and formats and all kinds of cool stuff. The next class, 28, will be over date functions, DateSerial, how to make ordinals, first, second, third, calculate age, list of birthdays, you name it.
Access Expert 27 and 28. If you like programming and VBA and want to learn more, I have tons of developer lessons available as well. Just check them out on my website.
There you go. That's going to do it for today. Folks, that is your TechHelp video. I hope you learned something. Live long and prosper, my friends. I'll see you next time.Quiz Q1. What is the main purpose of the EOMONTH function in Excel?
A. To find the last day of a given month, possibly offset by a specified number of months
B. To calculate the total number of days in any month
C. To find the first day of the current month
D. To subtract days from a date
Q2. When recreating the EOMONTH function in Access VBA, why is the second argument made optional?
A. To default to showing today's date if not specified
B. To allow defaulting to the end of the current month if no offset is provided
C. To force the user to provide a month offset
D. Because Access VBA does not support required arguments
Q3. In the custom EOMONTH function for Access VBA, what does "Public" mean?
A. The function can only be used inside the module
B. The function can be called from anywhere in the database
C. The function is visible on the Access main menu
D. The function is only available for forms
Q4. Which built-in VBA function is used in the Access EOMONTH implementation to manipulate dates?
A. DateDiff
B. FormatDateTime
C. DateSerial
D. Now
Q5. How does the DateSerial function calculate the last day of a month in the EOMONTH VBA function?
A. By adding one month and subtracting one day using zero for the day argument
B. By using the Day function to get the maximum day and subtracting one
C. By adding 30 to the original date
D. By using the Month and Year as offsets only
Q6. If the custom EOMONTH function is called with an offset of zero, what result does it return?
A. The last day of the next month
B. The first day of the current month
C. The last day of the current month
D. The current date
Q7. Which of these is NOT a correct way you could use the custom EOMONTH VBA function in Access?
A. Inside a query field expression
B. As a control source for a form textbox
C. In an external Excel workbook via VBA
D. In Access report calculations
Q8. Why is DateSerial(Year(D), Month(D) + MonthsToAdd + 1, 0) used in the function?
A. The 0 for the day returns the first day of the target month
B. Adding 1 to the month and using 0 for the day returns the last day of the original month plus MonthsToAdd
C. The function requires Month + MonthsToAdd to be divisible by 12
D. It always returns the first day of the previous month
Q9. What should you do if your Access database does not already have a global module?
A. Create a new Class Module for your function
B. Create a new standard Module through the Create tab
C. Add the function to any Form module
D. You cannot add custom functions
Q10. Why does the presenter suggest making the function name all capital letters (EOMONTH) in VBA?
A. So that Access recognizes it as a reserved word
B. Because VBA requires all function names to be uppercase
C. For consistency with Excel's function naming style
D. To prevent the function from being used outside VBA
Answers: 1-A; 2-B; 3-B; 4-C; 5-A; 6-C; 7-C; 8-B; 9-B; 10-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 TechHelp tutorial from Access Learning Zone covers how to recreate Excel's EOMONTH function - which stands for "End of Month" - using Microsoft Access VBA.
A common question among users transitioning from Excel to Access is whether it's possible to replicate familiar functions like EOMONTH in Access. For many, EOMONTH is a very useful tool in Excel. It returns the last day of a month, with the added flexibility to shift forward or backward by a specified number of months. The good news is that this functionality is quite straightforward to implement in Access using a custom VBA function.
Before getting started, I recommend reviewing a couple of my other videos if you're new to some Access techniques. First, consider watching my lesson on calculating the first and last day of a month in Access using the DateSerial function. In that video, I use queries without any VBA to get these dates. However, the EOMONTH function in Excel also lets you specify how many months into the future or past you want to go. That's the enhancement we'll be focusing on as we build out our custom VBA function in Access.
If you're not yet familiar with VBA programming, there's no need to be intimidated. I have an introduction to VBA video which covers the basics in about 20 minutes. It will give you all you need to understand and start writing your own VBA code. I also have a separate lesson on how to create your own custom functions within Access. All these tutorials are freely available on my website and YouTube channel.
To recap how EOMONTH works in Excel, if you have any given date and want to find the last day of the same month, you use the EOMONTH function. By passing the date and a zero for the number of months, the function returns the last day of that month. If you want the last day a few months ahead (for example, the last day of next month or three months out), you simply change the second argument.
In Access, we can mimic this process by designing our own EOMONTH function in VBA. I recommend putting this custom function in a global module so it's available throughout your database, including in queries, forms, and reports. If you don't already have a global module, you can easily create one from the Create menu by selecting Module (not Class Module).
When creating the function, remember that public functions in a module can be called from anywhere in your database. We're going to name our function EOMONTH, just as it appears in Excel, though the exact capitalization is not essential. The function will take two arguments: a date, and an optionally specified number of months to add. I suggest making the months-to-add parameter optional and defaulting it to zero, which covers the scenario where you want the end of the current month - a little improvement over the Excel version.
In the underlying logic, we'll use the DateSerial function in combination with the provided date and the number of months to add. One important detail is that DateSerial requires us to add one to the month and use zero for the day in order to return the last day of the previous month. This is a known trick with the DateSerial function and is something I cover in more detail in my related videos.
Once you've saved your function, you can quickly test it using the Immediate window in the VBA editor. By entering different dates and month increments, you can see that it reliably produces the last day of whatever target month you specify. Negative values will take you into previous months, while positive values will jump you forward.
You can now use your custom EOMONTH function across your entire Access project, making it as convenient to work with date calculations in Access as it was in Excel. If you're interested in even more date and time techniques, my Access Expert Level 27 covers a wide array of ways to work with dates and times. The following class, Level 28, goes even deeper, touching on topics like DateSerial, making formatted ordinals, calculating ages, listing birthdays, and much more.
For those looking to expand their Access development skills, I offer extensive training on both general and advanced VBA programming. All of these resources can be found on my website.
For a complete video walkthrough with step-by-step instructions on everything discussed here, visit my website at the link below. Live long and prosper, my friends.Topic List Overview of the Excel EOMONTH function
Explanation of EOMONTH function arguments
Using EOMONTH to find the last day of a month in Excel
Handling months offset parameter in EOMONTH
Formatting EOMONTH results as dates in Excel
Overview of building a custom VBA function
Creating a new module in Access VBA
Defining a public function in Access VBA
Writing the EOMONTH function in VBA
Making a function parameter optional in VBA
Implementing EOMONTH logic using DateSerial
Explanation of how DateSerial works for month end
Testing the custom EOMONTH function in the Immediate Window
Using the custom EOMONTH function with positive and negative offsets
Using the custom EOMONTH function throughout Access |
