Next Leap Year
By Richard Rost
3 years ago
Calculate the Next Leap Year Following a Given Year
In this Microsoft Access tutorial, I'm going to teach you how to calculate the next leap year following a given year in VBA. So for example, if you give it 2021, the function will return 2024.
Parker from Redondo Beach, California (a Platinum Member) asks: Thanks for the tutorial on determining leap years in Access. Your use of the IsDate function to create a query for leap years was really helpful. I'm curious, could we also use this method to calculate and display the next leap year from a given date? For instance, if I enter a date like "June 1, 2021", can Access calculate and show me that the next leap year would be 2024? Thanks in advance!
Members
Members will learn how to calculate the next leap year mathematically without using an iterative loop.
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 2016, Access 2019, Access 2021, Access 365, Microsoft Access, MS Access, MS Access Tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, calculate next leap year, Access VBA leap year tutorial, Microsoft VBA leap year logic, find next leap year code, leap year algorithm VBA, build leap year function Access VBA, user-defined leap year function Access, calculate leap year from date, next leap year from given date, leap years query Access, custom leap year function
Subscribe to Next Leap Year
Get notifications when this page is updated
Intro In this video, I will show you how to calculate the next leap year from a given date using Microsoft Access. We will review the leap year rules, discuss both mathematical and VBA-based approaches, and I will walk you through creating a custom VBA function to easily determine the next leap year. I will also demonstrate how to use this function in a query to display results based on your data.Transcript In today's video, I'm going to show you how to calculate the next leap year from a given date. For example, if you give it a date in 2003, it will return 2004.
I can always tell when a leap year is coming up because I get tons of emails and questions about leap years. It is currently 2023 and 2024 is a leap year.
Today's email comes from Parker in Redondo Beach, California, one of my Platinum members. Parker said, "Thanks for the tutorial on determining leap years in Access. Your use of the IsDate function to create a query for leap years was really helpful. I'm curious, could we also use this method to calculate and display the next leap year from a given date? For instance, if I enter a date like June 1, 2021, can Access calculate and show me the next leap year would be 2024? Thanks in advance."
Yes, of course. There are a couple of different ways you can do this. Let's talk about it.
But first, if you have not yet watched my leap year video, go watch this. In this video, I will show you how to use a simple query to determine if a current date is in a leap year. There is the link right there, or you can scan a little QR code, go watch that, and then come on back.
Let's take a quick second and review the rules for leap years. To be a leap year, a year has to be divisible by four, but not divisible by 100. Century years usually are not leap years, unless that year is also divisible by 400.
For example, 2024 is a leap year because it is divisible by four. 1900 was not a leap year because it is divisible by 100, but 2000 was a leap year because that is divisible by 400.
Why is this? Well, because the year is 365.256 sidereal days. That is how long it takes to orbit around the sun. Because of that little extra fraction of a day, they added these extra leap adjustments to the calendar. We actually insert leap seconds once in a while too because the Earth's rotation isn't perfect, but that is an astronomy lesson. This is an Access lesson. For all intents and purposes, these are the rules you have to remember.
I have this marked as a developer lesson because we are going to write a function in VBA to calculate the leap year. It is not that hard. Once you know the rules, it is actually relatively simple to write.
Can you do it without VBA? Yes, it is possible. You can mathematically calculate leap years, but the function is insane. Here it is. I will share it with you. That is a valid function right there to calculate the next leap year in a query field. No, I don't want to write that. I don't even want to read that. Does it work? Yes, it works. I tested it.
I will show you how to properly calculate it using VBA in a way that your brain can actually understand.
Since this is a developer lesson, if you have not done any VBA programming before, go watch this. It will teach you everything you need to know in about 20 minutes to get started programming in VBA. It is easy.
Also, go watch this video on creating a custom function. We are going to write our own function to calculate the next leap year. Also, go watch my Odd or Even video. This uses the Mod operator to determine if a number is evenly divisible by another number.
We are also going to use a Do Loop. Go watch this video on Do Loops. You can use any kind of loop. You can use a While loop. You can use a Do loop. You can use a For Each loop if you really want to, but I am going to show you a custom special instance of a Do loop.
Here I am in a TechHelp free template. This is a free database. You can download a copy from my website. Let's start by writing the function.
Come down here to our global module. In here, I am going to create my own function. Let's make it a Public Function and let's call it NextLeapYear.
I am going to take in a year and then respond with the next year after this year that is a leap year. You can very easily write it so it takes in an actual date or you can do whatever you want.
We are going to go MyYear As Long. I am going to return a Long.
Now, we are going to use a Do loop. We are going to go Do up here to start the loop and then Loop on the end. In my previous videos where we talked about loops, we did a Do Until loop and a Do While loop.
This is a special case of a Do loop where we do not have either Until or While, so it is effectively an endless loop. Yes, this is valid code. It is going to rely on you exiting the loop with an Exit Do inside of it.
Yes, I really did not cover this in my looping videos and I just thought about it today because I almost never use this. Usually, I will use a While loop. I will say something like While 1 = 1 and then Wend at the bottom, and then inside that loop, I will do an Exit While.
But I figured this is a chance to show you something new.
So we are going to say MyYear = MyYear + 1.
If you want to have the current year that is sent into the function able to be a valid response, then put this at the bottom of the loop. Do not add the year until the end of the loop. But I want to say that if I send in 2024, I want the next valid answer to be 2028.
It is all a matter of where you put the incrementer.
Next, we are going to say, what is the first rule? If MyYear Mod 4 = 0, then we have a year that is divisible by four.
Now, inside of that, we have to check to see if it is divisible by 100. If MyYear Mod 100 = 0, then we have to check for divisibility by 400. If MyYear Mod 400 = 0, then at this point we have a valid leap year because it is divisible by 400, like 2000. So here, we can Exit Do. It is going to keep the year we got.
If it is not divisible by 100, else, then we have another valid one, Exit Do.
And then we can End If here and End If there.
This is a perfect example of why you can see indentation is important for you. You do not need this indentation for Access for VBA; it does not care. This is so you can more easily read this. So if we have a year Mod 4 divisible by four, this whole thing runs. If it is not divisible by four, it just immediately increments to the next year: 88, 89, and so on.
Now, inside of here, we have a year that is divisible by four. First check, is it divisible by 100? If not, then exit out, we are good. That means you are divisible by four, not divisible by 100, you are good. 1996, for example.
But if it is divisible by 400, check to see if it is divisible by 400. If so, it is still valid, exit out.
Down here, all we have to say is, return the value for my function. So, NextLeapYear = MyYear, and that is it. There you go.
Let's throw in a Debug Compile, make sure everything compiles, save it, close it, and go test it with a query.
So, create Query Design, and let's bring in our customer table. We will do the same thing we did in the last video. We will use the Customer Since, which is a bunch of date fields. I am going to first say, give me the year of that.
Now, let's get the year. Y is going to be the year of Customer Since. If I run it now, I get a bunch of years. There is the year from that record. That is what we need to send in. You can just do it in one call if you want to. You could say, give me the NextLeapYear of Year of whatever, but this just makes it easier.
So, NextLeapYear: Let me zoom in, so you can see it better. Is my Zoom window big? It is smaller. Sometimes when I work on personal stuff, I make that Zoom window huge.
So, NextLeapYear, NLY, that is my query field, is going to be NextLeapYear of Y. If you want to skip a step, you can go Year of Customer Since. I am not going to do that. I already made Y. I like to use aliasing because it keeps your function calls short, especially when you get the same function you are using a million times.
Ready to go? Check out the results. Everything looks good.
Here is a 1995. That turns into 1996. Let's put in something from this year. Let's go 2023. Actually, let's put today's date in there. Good, 2024 comes up.
Let's put some tricky ones in here. So, 1999, 1/1, it does give 2000. That's good. How about 1899, 1/1, it does not give 1900. Because 1900 is divisible by 100, but not divisible by 400.
There you go.
As you can see, this is a lot easier for you to understand, to write, to read, and to comprehend. This does the same thing. It is just using IF functions and it is just, no.
Now, this does calculate the year mathematically instead of relying on an iterative loop. This is a loop. If you only have a handful of records, you are not going to be able to tell a difference with a modern computer. But if you have 10,000 or 100,000 records that you need to go through and calculate the next leap year for all of them, this might run slow, especially over a network.
In that case, you might want to use the mathematical version because it is faster. Or, stick around and check out the extended cut for the members where I will show you how to calculate it mathematically in VBA, which is a whole lot easier to read and comprehend.
We will do the same thing that this crazy function does, but we will write it in VBA so you can actually understand what it means. I mean, yes, I can understand this too because I have been doing this for so long. It is just not pleasant to look at. But we will do this in the extended cut. It is a good programming exercise.
Silver members can get access to all of my extended cut videos and there are tons of them, folks. There are lots of them. You get lots and lots of stuff for your membership.
Gold members can download these databases, and you have access to the Code Vault where all this cool VBA resides. Every function that I make that is cool, I put it in the Code Vault so you can just browse through them and pick which one you want.
That is going to be your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I will see you next time.Quiz Q1. What is the main goal of the video tutorial?
A. To teach how to determine if a given year is a leap year
B. To show how to calculate the next leap year from a given date
C. To display a list of all leap years in a custom table
D. To explain the history of the Gregorian calendar
Q2. According to the video, which sequence of steps correctly determines if a year is a leap year?
A. Divisible by 4 and 5, but not by 100, unless also divisible by 200
B. Divisible by 3, or by 100, but not 4
C. Divisible by 4, not divisible by 100, unless also divisible by 400
D. Divisible by 2 only
Q3. Why are leap years needed according to the lesson?
A. To adjust for Earth being closer to the Sun every year
B. Because a year is exactly 365 days
C. Because a year is about 365.256 days long, requiring occasional adjustments
D. To align the calendar with lunar cycles
Q4. In the VBA function described, which operator is used to check if a number is evenly divisible by another?
A. Divide (/)
B. And
C. Mod
D. Exp
Q5. What is a key benefit of using VBA for the NextLeapYear calculation as demonstrated in the video?
A. VBA is always faster than mathematical calculations in queries
B. VBA allows for clear, easily understandable code based on the leap year rules
C. VBA adds leap seconds automatically
D. VBA eliminates the need for any queries
Q6. According to the rules reviewed, which of the following years is a leap year?
A. 1900
B. 1999
C. 2000
D. 2100
Q7. In the function example, what does placing the MyYear = MyYear + 1 line at the top of the loop accomplish?
A. It allows the current year to be returned if it is a leap year
B. It allows skipping every other year in the loop
C. It ensures the function always returns the next leap year after the input year
D. It causes the loop to run only once
Q8. What structure does the video use within the VBA code to iterate toward the next leap year?
A. For Each Loop
B. Do While Loop with a loop condition
C. Do Loop without While or Until, exited with Exit Do
D. Recursive Function
Q9. What potential downside is discussed about using the iterative VBA loop for many records?
A. It is not compatible with modern versions of Access
B. It may run more slowly than a mathematical calculation method with large datasets
C. It does not work for dates after the year 2100
D. Access cannot call VBA functions from queries
Q10. What is the purpose of indentation in the VBA function code as described in the lesson?
A. VBA requires indentation for code to run
B. Indentation makes the code easier for humans to read and understand
C. Indentation speeds up code execution
D. Indentation allows the function to be run from a query
Answers: 1-B; 2-C; 3-C; 4-C; 5-B; 6-C; 7-C; 8-C; 9-B; 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 leap year from any given date using Microsoft Access. This is a common question that pops up, especially as we approach leap years like 2024. When someone provides a date such as sometime in 2003, they often want to know which year will be the next leap year, in this case, 2004.
I frequently receive questions around leap year calculations, and recently, one of my Platinum members asked if it is possible to use the same logic from my earlier leap year lesson to find and display the next leap year after a date entered in Access. For example, if you enter June 1, 2021, how can Access tell you that the next leap year will be 2024?
The good news is, yes, you can absolutely do this in a couple of different ways. Before we get into the solution, let's quickly review the leap year rules, because understanding these makes building the calculation straightforward. For a year to qualify as a leap year, it must be divisible by four. However, if a year is evenly divisible by 100, then it is not a leap year, except if it is also divisible by 400. For instance, 2024 will be a leap year since 2024 divides cleanly by four. The year 1900, however, was not a leap year since it is divisible by 100 but not by 400, whereas 2000 is a leap year because it is divisible by 400. This system helps the calendar keep pace with the actual time it takes Earth to orbit the sun—a little over 365 days—and from time to time, leap seconds are even added to keep things in sync.
In this lesson, we will write a function in VBA to calculate the next leap year. It is not particularly difficult if you follow the leap year rules. While it is possible to perform the calculation with only a query using an expression, the resulting formula is complicated and hard to follow. Instead, I will show you how to write the calculation in VBA so it is more logical and readable.
If you are new to VBA programming in Access, I recommend reviewing my introductory video to get up to speed. You might also want to watch my lesson on creating custom functions in VBA, as that skill will be important here. Knowing how to use the Mod operator (which checks if one number is divisible by another) will also come in handy, and you can brush up on that in my video about determining whether numbers are odd or even. Additionally, this lesson will use a Do Loop, a common structure in VBA for repeating actions until a condition is met. I suggest reviewing my video about Do Loops if you have not worked with loops before.
For this tutorial, I will use my free TechHelp template database, which is available to download from my website. To begin, I will write a Public Function called NextLeapYear in a global module. This function will take a year as input and output the next leap year that comes after it. With some minor adjustments, you could modify this function to accept a full date instead of just the year.
Inside the function, I use a Do Loop. Starting with the input year, I increment the year by one each cycle through the loop. If you wish to include the input year as a possible result, you would adjust when you increment the year. Here, I want only the next leap year, so if you supply 2024, the result will be 2028.
The function then applies the leap year rules: It checks if the current year is divisible by four. If so, it then checks if this year is divisible by 100. If the year is divisible by 100, then a final test checks if it is also divisible by 400. If it passes, that year is returned. If it is not divisible by 100, then as long as it is divisible by four, it is valid, and the loop exits. This approach keeps the structure clear and easy to read, which is helpful for maintenance and future changes.
After writing the function, I always recommend running a Debug Compile to catch any issues, then save and test it. I test the function in a query. For example, I can create a query that lists customers and the year they joined. By referencing this year in the new function, I can show the next leap year for each entry. Using aliases in the query fields can help keep your formulas short and easy to read, especially when you call the same function multiple times.
Testing with a variety of dates confirms the function works correctly for regular as well as special century cases. For example, entering 1899 returns 1904 (since 1900 is not a leap year), and entering the current year returns 2024.
While the VBA loop is much easier to follow and modify, it does perform the work step by step. For smaller sets of records, you will not notice a performance difference, but if you need to calculate the next leap year for tens of thousands of records—especially over a network—a direct mathematical formula in a query field may be more efficient. In today's Extended Cut, I will show members how to calculate the next leap year mathematically using VBA, similar to those complex query formulas, but in a way that is easier to read and maintain. This is a great programming challenge that will give you more insight into working with dates and conditions in VBA.
Silver members get access to all of my extended cut videos. Gold members can download the databases used in my lessons and explore the Code Vault, which contains many useful VBA functions you can browse and use in your own projects.
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 Overview of leap year rules
Writing a VBA function to find the next leap year
Using the Mod operator to check year divisibility
Constructing a Do loop in VBA
Incrementing years inside a loop
Nesting If statements for multiple conditions
Exiting a Do loop with Exit Do
Returning values from a custom VBA function
Testing the NextLeapYear function in a query
Using Year to extract the year from a date
Aliasing fields in Access queries
Handling edge cases like years divisible by 100 or 400
Optimizing function placement in query fields |
