Is Leap Year
By Richard Rost
4 years ago
How to Determine if a Year is a Leap Year in Access
In this Microsoft Access tutorial, I will show you how to determine whether or not a year is a leap year using the IsDate function and a few other tricks.
Pre-Requisites
Links
Recommended Courses
Methods
- Number of days in a banker's month (30)
- Number of days in the average month (30.4167)
- Number of days in the year (365 or 366)
- Number of days in the current month
Usage
- D: CustomerSince
- Y: Year(D)
- D2: [Y] & "-02-29"
- D2: "2/29/" & Y
- IsLeapYear: IsDate([D2])
- IsLeapYear: IsDate(Year([D]) & "-02-29")
- IsLeapYear: IsDate("2/29/" & Year([D]))
 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, leap year function, determine whether a year is a leap year or not, Code to calculate a leap year
Subscribe to Is Leap Year
Get notifications when this page is updated
Intro In this video, you will learn how to determine whether a given year is a leap year in Microsoft Access using the IsDate function. I'll show you step by step how to create a query that checks if February 29th exists for a particular year, use calculated fields and string concatenation, and explain some useful tips for working with date values in your database. We'll also look at both multi-step and single-formula solutions, plus a quick example of how to use this technique in Access VBA code.Transcript Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, we're going to learn how to determine leap years using the IsDate function in Microsoft Access. This is one that comes along every couple of years. Lots of people always ask me, how do you determine leap years in Access? In fact, this came up in yesterday's video because I show you how to do prorated rent, and sometimes landlords will calculate prorated rent based on the number of total days in the year instead of just the days in that month because it evens it out. That way, February isn't super bad and January is only 31 days and so on.
So you have to know, is it 365 days this year or 366 days? It could depend on the leap year. There are lots of other reasons why you might want to know if a particular February has 29 days in it.
Fortunately, there is a built-in function called IsDate that we can use to determine if a particular date is a valid date or not. If someone types something in and you want to know if it's a date, you can ask IsDate. How do we use this to determine leap years? Well, we can make a fake date, which would be February 29th of whatever particular year, and then ask IsDate: is that a valid date? If it says no, then 2-29-1987 doesn't exist, so it's not a valid date, and IsDate can tell you if that date is valid. So it's a tool for determining how leap years work.
Let's see how this works in the database. This is my TechHelp free template. You can download a free copy of this on my website if you want to.
A couple of prerequisites for you first. If you don't know how to use calculated fields and queries, go watch this. You should also know how to use the Year, Month, and Day functions. We're going to use the Year function to pull the year value out of any given date. If I tell it January 1st, 1980, it'll give me the 1980 part. Finally, we're going to use a little string concatenation that's used to put two strings together to use this little trick with IsDate.
If you don't know anything about those three topics, go watch those videos first. You'll find links to them down below in the description under the video window. Go watch those and then come back.
In my customer table, I've got a field with a whole bunch of dates in it. Let's see, right here, my CustomerSince field. We'll use that as the date we're trying to determine if that year is a leap year. Of course, I use the ISO date standard, which is year-month-day. It's universally accepted. I have students all over the world. There is one more video for you to watch if you're interested in that. This way, we're all on the same page when it comes to date format.
I want to see if that date, for example, happens to fall on a leap year. Let's make a query: Create - Query Design. I'll bring in my customer table and I'll close these little windows below me.
Let's find that CustomerSince field, bring that down here. Now, I don't want to have to keep referring to CustomerSince, so I'm going to alias that guy. I'm going to say D: In other words, I'm going to call CustomerSince D. This is shorthand. It's easier to type in my calculations here. Just refer to it as D, and you can plug any other value that you want in there.
I want to figure out what the year of that date value is. In the next field over here (Shift+F2 to zoom in), I'm going to call another variable Y, and that's going to be the Year of D. Take D, give me its year, and there you go. That guy there falls in 1989. There's a number representing that year.
Easy enough so far. That's the easy stuff.
Next up, we're going to use string concatenation to build a date that's going to be this year, followed by February 29th. Again, I'm using the ISO date standard, so I'm going to call it D2. Little nerdy Star Wars joke for you. This is going to be Y (that'll be 1990 or whatever), ampersand, then the string "-02-29". When you run it, there's your fake date. For example, 1990, June 12th, we've got 1990-02-29 for every year in this column.
Yes, just for the curious people, this will also work if you want to use your regional date format and you don't want to stick to the ISO dates. For example, if I said it was, let's say, 2/29/ and Y, this will also work. Like I'm in the US, if you want to use your local standard, you can do that too. But I'm ISO, so I'm going to stick with this one.
So we've got a whole column full of fake dates here. Now the trick is to see, using IsDate, if that's a valid date. Also, one more thing to notice: notice how these line up on the left side of the column; those on the right are numeric values, which include dates, currency, numbers - those are all numeric types. Versus text strings, which all line up on the left side. That's one giveaway right there: you can tell that's a text value.
The next step is to say, hey, is that a valid date? We're going to call it IsLeapYear. Zoom in again: IsLeapYear: We're going to use the IsDate function and put D2 in there. Is that guy there next to it that we just built a valid date? Now we'll run it, and there's a bunch of zeros and minus ones. Remember in Access, minus one is true or yes, zero is false. If you want to format that to show yes or no, you can right click, go to Properties, and then in Format, you can put yes/no, or true/false, or on/off, whatever you want. Lots of different formats are valid.
Now when I run it, I get yeses and noes. There you go. 2000 was a leap year. Remember that rule? It's different on those thousand years because if it's divisible by four, it is a leap year, unless it's divisible by 100, in which case, it's not a leap year, unless it's also divisible by 400, and then it is a leap year again.
Thank you for all the crazy date stuff or fantastic calendar.
Now you may be asking yourself: is there an easier way to write all this instead of having it all in multiple steps like that? Yeah, of course, the multiple steps are what I show you when I'm teaching you how to do something, so you can see how I got to where I got to, instead of just giving you one big function and saying, there it is. This way you see: we have to get the year, we have to make a date out of it, we have to check if that date's valid.
But if you want to boil this down into one statement you certainly can; it may make it harder for other people to read or for you in the future to figure out what you were doing. I've got a lot of stuff that I did years ago, and I look at it and I'm like, what? Sometimes if you do it in multiple steps like that, it's easier to figure out later.
But this will just boil down to IsLeapYear: IsDate, and then whichever date format you want. I'm going to go Year(D) and "-02-29", just like that. Run it, and there you go. Format as yes/no if you want to. There's your final function.
Can you use this in VB code? Certainly you can. Here, I wrote a little function for it right there. Very similar, same thing. This one's in the old US format. It's 17 months old. I did this before I switched to ISO, but it works either way.
Public Function IsLeapYear. You send it a year, like you'll send it 1980, and it will return a true/false value if that date is a valid date, if it's a leap year. Remember, this is in the Code Vault. You can go find it in the Code Vault. Another reason why it's beneficial to join. There are all kinds of cool little functions like this floating around in here.
If you want to learn more about this kind of stuff, I cover logical functions like IsDate, IsNull, IsNumeric, IsError, all kinds of the "is" functions in Access Expert 25. This is actually part one of my comprehensive guide to Access functions. Over several videos, I tear all the different functions apart. We go through logical functions. We go through string functions. We go through the math functions. We go through the type conversion functions. Date/time functions. We go through all the functions over the course of a couple hours, and I explain them all in detail.
That's one of the major reasons why you want to take my full course, because I cover things in the order you should learn them, not just randomly like I do with these Fast Tips.
But that is your fast tip for today. I hope you learned something. We'll see you next time.Quiz Q1. What is the main purpose of using the IsDate function in this video?
A. To check if a given value is a valid date
B. To convert numbers into dates
C. To format dates in ISO standard
D. To sort records by date
Q2. How does the video suggest you can determine if a year is a leap year in Access?
A. By dividing the year by 2 and checking the remainder
B. By checking if February 29 exists as a valid date using IsDate
C. By counting all February days
D. By checking if the year is a multiple of 1000
Q3. Which built-in Access function is used to extract the year from a date value?
A. Year
B. DatePart
C. Month
D. Day
Q4. What does the IsDate function return if the value being checked is a valid date?
A. Minus one
B. Zero
C. False
D. Error
Q5. In Microsoft Access, how are text values visually distinguished from numeric values in datasheet view, as demonstrated in the video?
A. Text values align right, numeric align left
B. Text values align left, numeric align right
C. Both are centered
D. Both align right
Q6. Why does the instructor recommend using multiple steps (fields) when building the leap year logic in a query?
A. For better performance
B. To show each step for teaching and for easier later review
C. To simplify the database schema
D. To avoid using functions
Q7. What date format does the instructor prefer to use when constructing fake dates in the query?
A. U.S. (MM/DD/YYYY)
B. ISO format (YYYY-MM-DD)
C. European format (DD/MM/YYYY)
D. Short date (MM/YY)
Q8. When combining the logic into a single step, how is the leap year check written?
A. IsLeapYear: IsDate(D & "-29-02")
B. IsLeapYear: IsLeapYearFunction(D)
C. IsLeapYear: IsDate(Year(D) & "-02-29")
D. IsLeapYear: IsLeap(D, 2, 29)
Q9. What is the result of applying the IsDate function to a non-existent date like 1987-02-29?
A. True/Yes/Minus one
B. False/No/Zero
C. Error message
D. Returns a blank value
Q10. According to the video, which of the following is TRUE about leap year logic?
A. Every year divisible by 4 is a leap year
B. Every year divisible by 100 is always a leap year
C. Years divisible by 4 are leap years, except those divisible by 100, unless divisible by 400
D. Leap years only occur every 1000 years
Q11. What is the benefit of formatting the IsLeapYear field in the query?
A. Changes its data type to text
B. Provides a visual result like Yes/No or True/False
C. Increases calculation speed
D. Hides the field from users
Q12. Can the IsDate leap year trick be used in VBA code?
A. Yes, you can use it in a VBA function
B. No, it only works in queries
C. Only in macros
D. Only in reports
Answers: 1-A; 2-B; 3-A; 4-A; 5-B; 6-B; 7-B; 8-C; 9-B; 10-C; 11-B; 12-A
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 covers how to determine leap years in Microsoft Access using the IsDate function. This question comes up frequently, since knowing whether a specific year is a leap year affects calculations for things like prorated rent. Landlords often want to know if the year has 365 or 366 days, and sometimes you may need to figure out if February has 29 days.
Microsoft Access includes the IsDate function, which checks if a value is a valid date. You can use this to determine leap years by creating a test date, such as February 29th for any given year, and then passing that to IsDate. If IsDate confirms the date is valid, then the year is a leap year. If not, it is not a leap year.
To demonstrate this, I used my TechHelp free template database. If you are not familiar with calculated fields and queries, or if you need a refresher on functions like Year, Month, and Day, I recommend watching some related videos first. You can find links to these videos on my website in the description.
Working with my Customer table and the CustomerSince field, which stores dates in the ISO format (year-month-day), I set out to see whether a date falls in a leap year. I started by creating a query and using aliases for my fields to make calculations easier. For instance, I called CustomerSince simply 'D' in the query.
Then, I used the Year function to extract the year portion from each date and called that value 'Y'. The next step involved building a string that represented February 29th for each year, using string concatenation. I called this field 'D2', and for each year, D2 held the value of the year followed by "-02-29" in ISO format. This can also be done using your regional date format if you prefer, but I stick to ISO as it is globally accepted.
After building this column of test dates, I used the IsDate function to see if each value represented a valid date. I called this field 'IsLeapYear'. When running the query, the results appear as zeros and minus ones, where minus one stands for true (yes, it is a leap year) and zero stands for false (not a leap year). You can format these results as 'Yes/No' or 'True/False' for easier reading. For example, the year 2000 shows 'Yes', confirming it as a leap year according to the leap year rules, which say a year is a leap year if divisible by 4, not a leap year if divisible by 100 unless it is also divisible by 400.
Although breaking the calculations down into separate steps makes it easier to understand and maintain, you can also condense everything into one calculation if you prefer. This can save space but might make the logic less clear when revisiting your work later.
For those interested in using this logic in VB code, you can write a simple function that returns true or false based on whether a given year is a leap year, following the same principle as above.
If you want to explore logical functions further, including IsDate, IsNull, IsNumeric, and others, I cover these topics extensively in Access Expert 25, my comprehensive guide to Access functions. Over several lessons, I go through all of the different categories of functions so you get a thorough understanding of how they work.
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 Determining leap years using IsDate in Access
Creating calculated fields in queries
Extracting the year from a date with the Year function
String concatenation to build date strings
Constructing a February 29 date for any year
Evaluating valid dates with the IsDate function
Formatting query results as Yes/No
Combining multiple steps into a single expression
Writing a custom VBA function to check leap years
Using the function in both query and VBA code |
