Access DateTime Seminar
Work with Advanced Date & Time Calculations
Welcome
In this Microsoft Access video seminar you will learn how to count the number of work days between two dates, determine if a date is a week day or a weekend day, calculate holidays, time stamp edited records, create popup notifications for reminders, generate recurring appointments, and calculate the number of a particular weekday (Friday) in any period (January).
 Resources
Main Seminar Goals
- Calculate Work Days Between Two Dates
- Duplicate the Excel NETWORKDAYS Function
- Create a Holiday Exclusion Table
- Time Stamp Records when Edited
- Create a Reminder Popup Notification Form
- Learn Form Timer Event Programming
- Prevent Popup Form from Stealing Focus
- Count the Number of a Specific Weekday in a Period
- Calculate Dates for All Major US Holidays
- Generate Recurring Appointments
- Display Ordinal Dates: 1st, 2nd, 3rd, etc.
Topics Covered
When I was putting together my Access Expert 27 and Expert 28 classes to cover DateTime functions, I received a lot of emails from students asking how to do certain things. Many of these things require at least a little VBA programming to accomplish, so I couldn't include them in the Expert classes. That's when this seminar was born.
We'll start off with the most requested question: how can I count the number of work days (M-F) between two dates. This is vital for calculating paid days for businesses, payroll, school attendence, and more. We'll start out by learning how to connect to the Excel function library and use Excel's NETWORKDAYS function. This is a quick solution, however it's not portable, and if you want to share your database with others, you'll need something built into Access.
So next we'll build our own replacement VBA function to count work days directly in Access. We'll also build a holiday exclusion table so your function will not count whatever days you specify, such as Christmas Day or the company picnic on a Tuesday. All of the code used in today's class will be included in the sample database you can download from my web site so you can copy and paste it right into your projects. Also, don't worry if you've never used VBA code before. I'll show you exactly how to put it in the database.
Next we'll create our own IsWorkDay function so you can quickly and easily check just one date to see if it falls on a work day or not.
The next topic is quite popular as well. People want to know how to tell when the last date/time a particular record was edited. It's easy to put a default value and a timestamp in to know when a record was created, but tracking when it was edited is not so easy. So, we'll learn how to use a form's OnDirty event as well as a table-based Data Macro to track edits to a record at both the form and table level.
Next we will learn how to create a reminder popup notification. You have a list of appointments, reminders, or "to-do" items, and you want to be notified when it's time to do something. Access is great for this. We'll create a reminder list, set a date/time for the reminder to appear, and then we'll learn how to use the OnTimer event and some VBA code to have this form check for when a reminder is coming up. Then, we just minimize this form and let it sit in the background, quietly counting down, checking for reminders once a minute (or however often you specify).
Then, when it comes time to notify you of an event, the reminder popup form appears. You can then click the Show Reminders button, or Remind me in 3 minutes and the form will go away for a few minutes, only to nag you again at whatever intervals you specify. We will take special care to program this form so that it opens up in the corner of your screen and does not interrupt what you're doing. So if you're typing away in your customer form, the reminder popup form will display, but it won't steal the focus and make you lose your place.
I know that I personally like to sit down with my reminder list and prioritize the day's work. I want the ability to go "I'll do that one in 5 minutes, this one in an hour, that tomorrow," and so on. To do this, we'll add some buttons to the bottom of our reminder form so we can move appointments ahead 5 minutes, 1 hour, or 1 day. Of course, you can customize these for whatever intervals you like. We'll program the reminder form to pause itself when you click anywhere on it using the OnActivate event, and then turn itself back on again when you click away from it with OnDeactivate.
People are always asking me to help them figure out "how many Mondays are in January 2015" or "how many Fridays are there in the 1st Quarter?" In the next lesson, we'll see how to perform these calculations. You can check the number of X days (Mon, Tues, etc.) in any interval you want (month, quarter, year, etc.) and check either the whole interval or just from a specific date to the end of that interval.
Going back to our holiday table in the first example, sometimes people need to be able to automatically calculate when those holidays occur without typing them into a table manually. Don't worry, we've got you covered. We'll learn how to calculate:
- Independence Day - July 4th
- Christmas Day - Dec 25th
- Labor Day - 1st Mon Sept
- Memorial Day - Last Mon May
- MLK Day - 3rd Mon Jan
- Presidents Day - 3rd Mon Feb
- Thanksgiving - 4th Thur Nov
- Daylight Saving Time Begin - 2nd Sun Mar
- Daylight Saving Time End - 1st Sun Nov
- Veterans Day - Nov 11, +/- 1 day
- Easter Sunday - Complex Calculation
Some of these are real easy, like Christmas Day (it's always December 25th). Some of them are not so easy, like Memorial Day (the last Monday in May). Some of them are really crazy, like Easter Sunday (the Sunday following the first Full Moon following the Vernal Equinox). We'll cover them all in this class.
Next we'll revisit our reminder form. People want to know how to take these reminders and automatically move them to a future date when they're finished. Some things like checking your email you may want to do every day. Other things like paying bills you might want to do once a month. So, we'll make the option to create recurring appointments. We'll make a "Done" button on our form. When that's clicked, the appointment will be moved ahead whatever time interval you specify, like "4 days" or "3 weeks."
When you get lots of appointments showing up, your list can be daunting, so we'll make text boxes you can use to filter the results. You can see all appointments up to today, just this week, or a custom date range. You can then click buttons to move up or down individual days or whole weeks. This makes it real easy to see this week's appointments, and then quickly scroll to next week or the week after that. And remember - these can now be recurring appointments, and you'll get reminders for them as long as your database is running. HINT: If you want to have the database automatically email or text you a reminder, just use the techniques I cover in my Access Email Seminar.
Finally, people want to be able to display dates as "Monday, January 5th, 2015." These are called ordinal numbers: 1st, 2nd, 3rd, etc. We'll have a lesson where I'll show you how to make a custom function to display these kinds of numbers.
If you want to be able to work with DateTime values Access, this is the perfect seminar for you. You will learn everything mentioned above. You will have access to the full database that we build in class with all of these features. If you have any question whether this seminar is right for you, please feel free to contact me.
Pre-Requisites
It is strongly recommended that you have completed my entire Access Beginner and Expert series before taking this seminar.
Version
I am using Access 2013 in this seminar, however the lessons are perfectly valid for all versions of Access. It's currently 2022 and I just made sure everything works fine with Access 2019 and Office 365.
Enroll Today
Enroll now so that you can watch these lessons, learn with us, post questions, and more.
Questions?
Please feel free to post your questions or comments below. If you are not sure as to whether or not this product will meet your needs, I'd rather help you before you buy it. Remember, all sales are final. Thank you.
Keywords
microsoft access work days, week days, holidays, time stamp, reminder popup notification, days per month, recurring appointments, reminder popup
Transcript Welcome to the Advanced Date Time Seminar, brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
Today's seminar is going to cover a bunch of advanced techniques that have to do with dealing with dates and times. These are a lot of questions that I got asked during my normal Access Expert classes, Levels 27 and 28, where I covered the date time functions. All of these techniques require at least a little bit of programming, and in my Access Expert series, I am trying to avoid using VBA programming, so I decided to put all this together in a seminar for you.
We are going to cover popular topics such as calculating the number of workdays between two dates. For example, give me January 5th and February 5th. I want to know how many Monday through Friday days there are in there, and I will show you how to exclude holidays by making a holiday table.
We will learn how to date/time stamp edited records. It is very easy to figure out when a record is created, but what if you want to know when that record was edited? I will show you how to time stamp it as soon as it is edited.
Lots of people want to know how to generate pop-up reminder notifications in Access, so you can be working on something, and then all of a sudden your database will go, oh, you have a reminder, you have to make a phone call in five minutes. Those are pop-up reminders.
I will show you how to figure out how many Mondays are in January, or how many Tuesdays are in February, or how many Thursdays are in the third quarter. We will do all kinds of different calculations like that, that involve counting the number of a particular type of day between two date ranges.
We will learn how to calculate holiday dates. Give me a year, and I will tell you what day the easy ones fall on, like Christmas or New Year's Day, those are simple. The more complicated ones, such as Martin Luther King Day or Thanksgiving Day, and we will even see how to calculate the really crazy ones. Like Easter Sunday has a very complicated formula. We will take a look at that later.
Finally, a popular request - we will see how to create recurring appointments. We will make a list of appointments or reminders in our database. We will set a frequency, such as "I want to do this every three days or every two weeks." When we mark it done, the database will automatically advance it that far into the future.
So, lots of different cool techniques today, and again, all of these involve a little bit of programming, but that's okay. Don't worry if you've never done any programming before; I'll show you everything you need to know step by step.
Again, I mentioned the prerequisites earlier: Access Expert 27 and 28. I strongly recommend before taking this class, that you have taken my complete beginner series in Access and all of my expert classes, levels 1 through 28.
In 27, I cover the basic date/time functions, Date, Time, Now. I teach you about date mathematics, and we will build a simple accounts receivable with date aging in it, so I can say, "Show me all the orders that are 30 days past due, 60 days past due," and so on.
Then, in Expert 28, we go through all of the date/time functions in detail, the more advanced ones: Day, Month, Year, Hour, Minute, Second, Weekday, WeekdayName, MonthName, DateAdd (for adding dates), DateDiff (for calculating the difference between two dates), DateSerial, DatePart, DateValue, TimeValue, and ConvertDate.
A helpful, but not required, prerequisite is my Access SQL Seminar Part 1. When we build our reminder popup form, I'm going to use a little bit of SQL in there to dynamically recreate the form and show some data on there in different ways. It will be helpful if you've taken the SQL Seminar first, but not required. Again, I'll show you everything you need to know today. If you want to learn more about SQL, I recommend taking that first before this one.
This seminar was recorded with Access 2013. I am pretty sure everything that I cover will work in 2010 and 2007 as well. Most of the VBA, if not all of the VBA, will work with 2003 and earlier, but the lessons that talk about embedded macros, for example, the timestamp, will not work. You'll have to use the VBA code lesson for that.
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're 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've 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's 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'll see that it's 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're going to cover in today's class.
One of the most popular questions I get with Microsoft Access when it comes to date/time functions is, "How do I replicate the NETWORKDAYS function from Microsoft Excel?" NETWORKDAYS in Excel allows you to enter two dates, and it will return the number of workdays (Monday through Friday) plus an optional list of holiday dates. It will tell you how many workdays are in that range. Access does not have that functionality.
So in lessons one, two, and three, I am going to show you how to build something like that in Microsoft Access. In lesson two, we continue on with the workdays function. Lesson three concludes the three lessons on the NETWORKDAYS function. When this lesson is finished, we will not only have a function that mimics the NETWORKDAYS function of Excel, where you can count the number of workdays between two dates, but we will also build our own ISWORKDAY function, where we can check any single specific date to see if that date is a workday, whether it's a Monday through Friday or it's on our holiday list of excluded dates, and so on.
In lesson four, we are going to learn how to time stamp records when they're edited. So when someone makes a change to a record, either in the table or the form, we'll set a "Last Updated" field equal to the current date and time.
In the next couple of lessons, we are going to learn how to make event pop-up notifications. We will make a list of reminders, what the notification date and time is, and then we will make a form that will pop up automatically and say, "Hey, you've got a reminder four or five minutes from now."
In lesson five, we will begin by learning how to program the OnTimer event. In lesson six, we continue with the reminder pop-up notifications (this is part two) and build an actual reminder pop-up form. In lesson seven, we continue with our reminder pop-up notifications (this is part three). We work on the Remind Me button, so you can say, "Remind me in five minutes and ten minutes," and then move the reminder buttons to move it to tomorrow, next week, and so on.
In lesson eight, I am going to show you how to calculate how many X days are in a particular interval. Now, by X day, I mean a specific day of the week (for example, how many Wednesdays are in this month, how many Thursdays are in next quarter). This is an example that I did not cover in Access Expert 28 because it involves some programming, so I saved it for the seminar.
In lesson nine, we are going to learn how to calculate holiday dates. We will start off with some easy ones, like U.S. Independence Day and Christmas Day. Then we will get into some more complex ones, like President's Day (the third Monday of February) or Labor Day (the first Monday of September). Then we will get into some really crazy stuff, like how to calculate Easter Sunday.
In lesson ten, we are going to take our reminders form that we created in an earlier lesson. We are going to set it so that each of our reminders can be recurring, so I can say, when I click the Done button, "Move this ahead three days, or two weeks, or four months," or whatever. This is good for reminders to pay bills, or to check your YouTube channel, or any kind of regular recurring reminders you have. We will also make buttons so we can advance through, show me this week, last week, the week after that, and so on.
In lesson eleven, we are continuing with our recurring appointments form.
In lesson twelve, I will show you just a real short function that I wrote a while back to display ordinal dates (that's first, fifth, seventh, thirty-first, and so on).
Thank you. |
