Introduction

Welcome! DateAdd, DateDiff & DatePart Guide

S M L XL FS | Slo Reg Fast 2x | Bookmark

Welcome to Microsoft Access Expert Level 28. In this course we will continue our comprehensive function guide by focusing on advanced date and time functions in Microsoft Access. We will walk through breaking apart dates into components, using the Weekday, DateAdd, DateDiff, DatePart, and DateSerial functions, and learn how to calculate things like the day of the week, intervals between dates, and ordinal date display. We will also discuss generating lists of upcoming birthdays, table-level validation rules, and where to ask questions or download the sample database to follow along.

Navigation

Prev: X28
Next: Lesson 01

Keywords

Access Expert, date functions, time functions, DateAdd, DateDiff, DatePart, DateSerial, Weekday function, breaking apart dates, calculate age, ordinal dates, table validation rules, birthdays by month, intervals, month day year extraction

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

Welcome to Microsoft Access Expert Level 28 brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.

Today's class is part 4 of my Comprehensive Function Guide to Microsoft Access. Part 1, which was Access Expert Level 25, covered string and logical functions. Part 2 covered the math and type conversion functions. Part 3, the last class, Access Expert 27, was the first part of the date and time functions, and then I realized I had to split it into two, so this is Expert 28, which covers the rest of the date and time functions. Consider 27 the introduction, and 28 is the meat and potatoes of the date and time functions.

Just as a quick mention, I have also recorded an advanced date and time seminar that covers date and time functions that are even more advanced than I show in today's class, because there are some additional things that require VBA programming, and I will talk about these in the last lesson of the class in detail.

Before taking this class, I strongly recommend you watch Access Expert 27 first, which covers the introduction to dates and times. If you do not understand dates and times properly, as explained in 27, you are really going to be lost in this class. Of course, before 27, I recommend the entire beginner series and all of my Access Expert levels, one through 26, before that.

This class was recorded using Access 2013. I am pretty sure everything covered today works in 2010 and 2007 as well. I am pretty confident that most of what I show today also works in 2003 and earlier, but I cannot guarantee that. I do not have any versions of Access on my machines before 2013 anymore, so I really cannot even test it. But if you are using an older version of Access and you come across something that does not work, please let me know, and I will see if we can find a workaround for you.

My courses are broken up into beginner, expert, advanced, and developer level classes. Beginner level classes are for novices. You should understand all the topics covered in them by the time you get to the expert level classes, which you are in now. When you finish all of the expert level classes, the advanced classes will cover event programming and macros, and the developer classes will cover Visual Basic for Applications. Each group of classes is broken down into multiple levels, level 1, 2, 3, and so on.

In addition to my normal Access classes, I also have seminars designed to teach specific topics. Some of my seminars include building web-based databases, creating forms and reports that look like calendars, securing your database, working with images and attachments, writing work orders and running a service business, tracking accounts payable, learning the SQL programming language, creating loan amortization schedules, and lots more.

You can find details on all of these seminars and more on my website at accesslearningzone.com.

If you have questions about the topics covered in today's lessons, please feel free to post them in my student forums. If you are watching this course in the online theater on my website, you should see the student forum for each lesson appear in a small window next to the class video. Here you will see all of the questions that other students have asked as well as my responses to them and comments that other students have made. I encourage you to read through these questions and answers as you start each lesson and feel free to join in the discussion. If you are not watching these lessons on my website, you can still visit the student forums later by visiting accesslearningzone.com/forums.

To get the most out of this course, I recommend you sit back, relax, and watch each lesson completely through once without trying to do anything on your computer. Then, replay the lesson from the beginning and follow along with my examples. Actually, create the same database that I make in the video, step by step. Do not try to apply what you are learning right now to other projects until you have mastered the sample database from class.

If you get stuck or do not understand something, watch the video again from the beginning or tell me what is wrong in the student forum and I will do my best to help you. Most importantly, keep an open mind. Access may seem intimidating at first, but once you get the hang of it, you will see that it is really easy to use.

Now, I strongly encourage you to build the database that I build in today's class by following along with the videos. However, if you would like to download a sample copy of my finished database file, you can find it on my website at accesslearningzone.com/databases. Sometimes, if you get stuck, the easiest way to learn is to tear apart someone else's database.

One of the ways that I taught myself Access years ago was by tearing apart the Northwind Traders database that comes with Microsoft Access. You will find there is a sample database for each of my courses on my website.

Now let's take a few minutes and go over exactly what we are going to cover in today's class.

In lesson one, we will learn how to break apart dates into their components: month, day, year, hour, minute, second, and so on. We will learn about the Weekday function, which lets us determine what day of the week a particular date falls on. We will use that to calculate the first day of the week, the last day of the week, the Tuesday before today, and so on.

In lesson two, we are going to learn about the DateAdd function. This is where you can add a number of intervals to a date value, and an interval can be a day, a week, a month, a quarter, a year. So you can say, for example, what is exactly two months, two calendar months, from today, or what is one year from yesterday?

In lesson three, we are going to learn the difference between two dates, and not necessarily just in days. You can say, show me how many weeks there are between these two dates, or how many calendar months are between these two dates. That is the DateDiff function.

In lesson four, we are going to learn about the DatePart function. Now DatePart is similar to some of the other functions that we have used, in that you can use DatePart to break a date apart into its different components. However, DatePart has a lot of advanced functionality, and we can use it to determine things like whether a date falls within the previous month, the next month, the previous quarter, and so on.

In lesson five, we are going to learn about the DateSerial function. Now DateSerial lets us take the individual components, day, month, year, and put them together to get a valid date back. That does not seem like a lot, but we can use DateSerial to calculate a lot of different cool stuff. For example, what is the last day of the previous month? What is the first day of the next quarter? We can use it to calculate someone's exact age, which is very difficult to do without the DateSerial function. So, there are lots of cool things you can do with DateSerial.

In lesson six, I am going to teach you how to display the day of the month as an ordinal date: first, second, third, and so on.

In lesson seven, we have a couple of other miscellaneous topics to go over. I am going to show you how to calculate a list of birthdays coming up next month. So, if it is currently January and you want to see all of your customers who have February birthdays, we will generate that list. We will also talk about table-level validation rules. That allows you to have validation rules that are checking multiple fields. So you can say things like the ship date has to be later than the order date; otherwise, you cannot save the record.

Quiz

Q1. What main topic does Access Expert Level 28 focus on?
A. Date and time functions in Microsoft Access
B. String manipulation functions
C. Security and permissions in Access
D. Exporting reports to Excel

Q2. Which function allows you to determine what day of the week a specific date falls on?
A. Weekday
B. DateAdd
C. DateDiff
D. DateSerial

Q3. What is the recommended prerequisite before taking Access Expert Level 28?
A. Access Expert Level 27
B. Access Advanced Level 2
C. Access Beginner Level 1
D. VBA Programming

Q4. The DateAdd function is used to:
A. Add a specific interval to a date value
B. Break a date into its components
C. Calculate the difference between dates
D. Display dates as ordinal values

Q5. Which function can be used to calculate the number of weeks or months between two dates?
A. DateDiff
B. Weekday
C. DateSerial
D. DateAdd

Q6. What does the DatePart function do?
A. Returns a specific part or interval of a date
B. Adds days to a given date
C. Subtracts one date from another
D. Converts a string to a date

Q7. How does the DateSerial function differ from DatePart?
A. DateSerial puts together date components into a valid date
B. DateSerial only returns the month from a date
C. DateSerial breaks a date into parts
D. DateSerial displays the weekday name

Q8. Which lesson teaches how to display ordinal dates (first, second, third, etc.)?
A. Lesson six
B. Lesson one
C. Lesson three
D. Lesson eight

Q9. Table-level validation rules allow you to:
A. Validate relationships between multiple fields
B. Format text fields
C. Change field data types
D. Create summary queries

Q10. What is Richard Rost's advice for students starting each lesson?
A. Watch all lessons once, then follow along step-by-step to build the sample database
B. Try to apply concepts to other databases immediately
C. Only read the textbook
D. Skip the sample database creation

Q11. If you get stuck or do not understand something in the course, what does Richard recommend?
A. Watch the video again from the beginning or use the student forum for help
B. Ignore the topic and move on
C. Ask other students outside the forum
D. Only use external websites for answers

Q12. Where can you find additional sample databases used in the courses?
A. accesslearningzone.com/databases
B. northwind.com
C. office.com/templates
D. msaccesshelp.com

Q13. Which of the following is NOT specifically mentioned as a topic in lesson seven?
A. Calculating upcoming birthdays
B. Table-level validation rules
C. Creating reports from queries
D. Checking that ship date is after order date

Q14. If using a version of Access prior to 2013 and you encounter problems, what should you do?
A. Contact Richard for help finding a workaround
B. Uninstall your version and install Access 2013
C. Skip the problematic topic
D. Buy a new computer

Q15. What is a suggested strategy for learning Access according to Richard?
A. Take apart sample databases to understand how they work
B. Memorize all function syntax from the manual
C. Only watch videos but do not practice
D. Focus only on theoretical knowledge

Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-C; 14-A; 15-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 is Microsoft Access Expert Level 28, which is the fourth part in my Comprehensive Function Guide series. I am your instructor, Richard Rost.

To recap, Expert Level 25 started the series with string and logical functions. The next class covered the math and type conversion functions. In Expert Level 27, I introduced date and time functions, but the topic ended up being large enough to require splitting into two parts. So, this course, Expert Level 28, will focus on the remaining date and time functions. Think of Level 27 as your foundation and Level 28 as the main course for working with dates and times in Access.

I also want to mention that I have an advanced date and time seminar available. That seminar goes even more in depth than what I cover today. The more advanced concepts require VBA programming, so I will mention those toward the end of this class.

Before getting started, I highly recommend you watch Expert Level 27 if you have not already, especially if you are not completely comfortable working with dates and times in Access. If those concepts are not clear, you will have a hard time with this lesson. Of course, you should also follow the entire Beginner series and all previous Expert levels up through 26.

This class was recorded using Access 2013, but almost everything should work fine in 2010 and 2007, and likely most of it in 2003 or earlier. I just cannot guarantee that everything will function in versions earlier than 2013, because I do not have those old versions to test anymore. If you find that something does not work in your version, reach out to me and I will help you try to find a solution.

Let me explain how my courses are organized. Beginner classes are for people new to Access. By the time you reach the Expert level, you are expected to understand all beginner material. The advanced classes come after Expert and deal with macros and event programming, and the Developer-level classes cover VBA. Each track is divided into multiple levels, so you can learn at your own pace.

Besides these classes, I also offer specialized seminars on topics like building web-based databases, creating form and report calendars, securing your database, managing images and attachments, generating service business work orders, accounts payable tracking, learning SQL, setting up loan amortizations, and much more. You can get details about all of these seminars on my website at accesslearningzone.com.

If you have questions while you are following along, I encourage you to participate in the student forums. If you are watching on my website, the forum for each lesson appears next to the video, so you can see questions other students have asked along with my answers and responses from other students. Be sure to review these discussions as you go. If you are watching elsewhere, you can still read and post in the forums later at accesslearningzone.com/forums.

For best results, I suggest you first watch each lesson all the way through without trying to follow along or do anything in Access. After you have seen the lesson, start from the beginning and walk through the database examples step by step as I demonstrate. Replicate what I am doing exactly, and do not try to apply these concepts to your other projects until you are comfortable building the sample database from the class.

When you get stuck or do not understand part of a lesson, go back and watch the video again, or ask for help in the student forum. Let me know exactly where you are having trouble and I will do my best to answer your questions. Most importantly, keep an open mind and be patient with yourself. Access may appear challenging at first, but once you get past the learning curve, you will see just how logical and straightforward it can be.

Although I always encourage students to create the sample database from scratch along with me, you can also download a finished copy of the database file for each course from my website at accesslearningzone.com/databases. Sometimes, looking over someone else's finished work can help clarify how a solution is built, and tearing apart existing databases is how I learned many of these concepts myself. The classic Northwind Traders database that comes with Access is a great resource as well.

Now let me preview what we will be covering in today's class.

In the first lesson, we will learn to extract different components from a date, like the month, day, year, hour, minute, and second. We will also explore the Weekday function to identify the day of the week for any date and use it to calculate specific dates, such as the first or last day of the week or a specific weekday before or after today.

The second lesson is all about the DateAdd function. This function lets us add or subtract date intervals, whether that is days, weeks, months, quarters, or years. For example, you will see how to find out what the date will be exactly two months from now or one year before yesterday.

Lesson three introduces the DateDiff function, which calculates the difference between two dates not just in days, but also in weeks, months, or other intervals, as needed.

In lesson four, we shift to the DatePart function. While similar to other date functions, DatePart adds advanced capabilities, such as helping us determine if a date falls within a previous or next month, quarter, or other date groupings.

Lesson five covers the DateSerial function. DateSerial takes day, month, and year components and builds them back into a valid date. While that might seem straightforward, it opens up possibilities for all sorts of calculations like finding the last day of the previous month, the first day of the next quarter, or even getting an exact age based on a birth date. Some of these tasks would be quite difficult without DateSerial.

In lesson six, I will show you how to display dates as ordinal numbers, so you can show 'first', 'second', 'third', and so on for the day of the month.

Finally, in lesson seven, we will wrap up with some miscellaneous topics. I will explain how to create a list of upcoming birthdays for the next month, which is handy if you want, for instance, to contact customers with birthdays in February. We will also look at table-level validation rules, which can be used to enforce rules involving multiple fields, such as making sure a ship date comes after the order date.

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

Breaking apart dates into month, day, year, etc.
Using the Weekday function to find day of week
Calculating first and last day of the week
Finding specific days like the previous Tuesday
Using DateAdd to add intervals to a date
Calculating dates like two months from today
Using DateDiff to find difference between two dates
Finding number of days, weeks, or months between dates
Using DatePart to extract components from a date
Determining if a date is in previous or next month/quarter
Using DateSerial to build dates from parts
Calculating last day of previous month with DateSerial
Finding first day of next quarter with DateSerial
Calculating exact age using DateSerial
Displaying ordinal day numbers (first, second, third)
Listing birthdays occurring next month
Using table-level validation rules for dates
Ensuring ship date is after order date in validation

Article

Welcome to an in-depth guide on advanced date and time functions in Microsoft Access. In this tutorial, I will walk you through a range of powerful tools that can help you manipulate and analyze dates and times in your Access databases. Understanding these functions will allow you to break apart dates into their components, calculate intervals, and validate your data effectively.

To start, one of the most useful things you can do with dates is to extract their individual components. For example, if you have a full date and you want to know what day, month, or year it falls in, Access offers specific functions for this. The Year function gives you just the year from a date value. For example, the expression Year(#6/15/2020#) will return 2020. Similarly, the Month function returns the month number, so Month(#6/15/2020#) would return 6. The Day function extracts the day of the month, so Day(#6/15/2020#) gives 15. If your data also includes time, you can use the Hour, Minute, and Second functions. For example, Hour(#6/15/2020 14:30:00#) returns 14.

A particularly helpful function for understanding dates is Weekday. Weekday returns a numeric value indicating the day of the week for a given date. By default, Sunday is 1, Monday is 2, and so on. For example, Weekday(#6/15/2020#) returns 2, because June 15, 2020 was a Monday. If you want to adjust which day of the week is considered the first, you can pass a second parameter. For example, Weekday(Date(), vbMonday) will consider Monday as 1.

Once you know how to identify a day of the week, you can calculate other relevant dates, such as the first day of the current week or the last day. For example, suppose you want to find the Sunday before today. You could use Date() - Weekday(Date(), vbSunday) + 1. By manipulating this expression, you can find the Monday of the current week or calculate the date for any other weekday relative to today.

Moving on, the DateAdd function allows you to add or subtract time intervals to a specific date. The basic syntax is DateAdd("interval", number, date). The "interval" is a string code representing the type of interval, such as "d" for days, "m" for months, "yyyy" for years, and so forth. For example, to find the date two months from today, use DateAdd("m", 2, Date()). If you want to find yesterday's date, use DateAdd("d", -1, Date()).

To determine the difference between two dates, Access provides the DateDiff function. DateDiff takes the same kind of interval code as DateAdd, plus two date values, and tells you how many of those intervals fall between the two dates. For example, DateDiff("d", #6/1/2020#, #6/15/2020#) returns 14, since those dates are 14 days apart. If you instead use DateDiff("m", #1/15/2020#, #6/15/2020#), you get 5, since there are five calendar months between the two dates.

For more advanced analysis, DatePart can break a date into any component, similar to the Year, Month, and Day functions, but with extra options. Using DatePart("q", [DateField]), you can find the quarter of the year for a given date. DatePart can segment dates by week, quarter, day of the year, and more, depending on what you need.

If you ever need to put a date together from its parts, DateSerial is the tool for that. The syntax is DateSerial(year, month, day), and it returns a date value. For example, DateSerial(2020, 6, 15) returns June 15, 2020. This lets you calculate complex date logic. For example, to get the last day of a given month, use DateSerial(Year(d), Month(d) + 1, 0), where d is your starting date. This works because specifying day 0 for the next month returns the last day of the previous month. You can also use DateSerial to determine someone's exact age, which is useful because simple subtraction often does not account for leap years or partial months. For example, to calculate the age, you can use:

Age: DateDiff("yyyy", [BirthDate], Date()) - IIf(Format([BirthDate], "mmdd") > Format(Date(), "mmdd"), 1, 0)

This expression subtracts an extra year if the person's birthday has not yet occurred in the current year.

If you want to display dates as ordinal numbers, such as 1st, 2nd, 3rd, and so on, you can use a small piece of VBA code to attach the correct suffix. For example, you might write a function like this:

Function OrdinalSuffix(n As Integer) As String
Select Case n Mod 10
Case 1
If n Mod 100 <> 11 Then
OrdinalSuffix = n & "st"
Exit Function
End If
Case 2
If n Mod 100 <> 12 Then
OrdinalSuffix = n & "nd"
Exit Function
End If
Case 3
If n Mod 100 <> 13 Then
OrdinalSuffix = n & "rd"
Exit Function
End If
End Select
OrdinalSuffix = n & "th"
End Function

You can then use OrdinalSuffix(Day([DateField])) in your queries or reports to show "1st", "2nd", or "3rd".

Sometimes, you might want to generate a list of all customers whose birthdays occur next month. To do this, compare the month portion of each birthday to the month value one month from today:

Month([BirthDate]) = Month(DateAdd("m", 1, Date()))

This query will return everyone whose birthday is in the upcoming month.

Data validation is important to ensure accuracy in your database. Access allows you to create validation rules at the table level that check the logical relationship between fields. For example, to ensure that a shipping date always comes after an order date, use a validation rule like:

[ShipDate] > [OrderDate]

This will prevent a record from being saved if the ship date is not later than the order date.

By combining these functions and techniques, you can manage dates and times with precision in Access. Whether you are calculating future events, analyzing date ranges, breaking dates into parts, or joining those parts back together, these tools provide you with the flexibility you need. The more you experiment with these functions, the more comfortable you will become working with date and time values in your own projects. If you need sample databases to practice or want to learn by reverse engineering, Microsoft provides the Northwind sample database, and there are other resources available online to help you get started. The key to mastering date and time functions in Access is practice and experimentation, so try the examples above and see how they apply to your own data.