Access 2007-2019
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
Home   Courses   Seminars   Templates   TechHelp   Fast Tips   Forums   Help   Contact   Join   Order   Logon  

Access DateTime Seminar

Learn how to work with advanced date & time calculations in Access 

DateTime Seminar

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). Topics include:

- Calculate Work Days Between Two Dates
- Duplicate the Excel NETWORKDAYS Function in VBA
- Create a Holiday Exclusion Table for Work Day Results
- 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.

Click here to watch the first full lesson of the Seminar in our Online Theater:


Access DateTime Seminar
Description: Learn how to work with advanced date/time functions and programming in Access
Versions: I use Access 2013, however most of the material covered should work fine with most versions of Access.
Pre-Requisites: This course stands alone, however it is very strongly recommended that you have taken Access Expert 28 before beginning this class.
Running Time: 5 Hours, 22 Minutes
Cost: $129.99 - Order multiple courses to receive a discount up to 50% off


Here's What You'll Learn

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.

count work days


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.

count work days


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.

is work day


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.

timestamp record edits


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).

popup reminder notifications


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.

reminder form


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.

reminder form


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.

days in month


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.

calculate holidays


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."

recurring appointments


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.

reminder dates


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.

ordinal numbers 1st 2nd 3rd


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.


Access DateTime Seminar Outline

00. Intro (11:41)

01. Number of Work Days 1 (20:19)
Excel NETWORKDAYS Function
Create a Module
Reference to Microsoft Excel 15.0 Object Library
Create myNetWorkDays Function
Remove the Excel Reference and Code

02. Number of Work Days 2 (21:05)
While Loop Date Counter
Count Number of Days Between Dates
WeekDay Function to Determine Day of Week
Holiday Exclusion Table
DLOOKUP to Check Holiday Dates
Dealing with #Error problem

03. Number of Work Days 3 (22:20)
Using DCOUNT Outside Loop for Speed
Validation Rule to Prevent Sat Sun in Table
IsWorkDay Function to Check One Date

04. Timestamp on Edited Records (16:40)
When was Customer Record Last Updated
Create Customer Table and Form
Use Default Value to Track Creation Date
Record Change to a Single Field
Record Changes to Multiple Fields
Field AfterUpdate Event
Record Changes to Entire Record on Form
Form OnDirty Event
Tracking Changes at the Table Level
Table Data Macros
Create Data Macro
Before Change Event
SetField Command

05. Reminder Popup Notifications 1 (27:32)
Create Reminder List Table
Reminder Text, ReminderDateTime, Closed
DLOOKUP to Check for Reminders
Closed=FALSE and Reminder Now or in Past
Conditional Formatting to Highlight Due Reminders
Checking for Reminders Automatically
OnTimer Event
Timer Interval Milliseconds
Constant MAXVAL
Countdown Loop for Timer
IsPaused Checkbox

06. Reminder Popup Notifications 2 (22:57)
Open Reminder Form if there are Reminders
Special Popup Reminder Form
Preventing the Popup from Interfering with Typing
SetFocus Method
Set TimerInterval to Zero to Disable
You have 3 Reminders
DCOUNT to Count Number of Reminders
Label.Caption Property
Button to Show Reminder Form
Check to see if Reminder Popup Form is Open
IsLoaded Property

07. Reminder Popup Notifications 3 (29:41)
Remind Me Later Button
Remind Me in X Minutes
Access Commands Not Available Bug!
Have the Timer Pause Itself if On Reminder Form
OnActivate Event
Have the Timer Restart When Leave Form
OnDeactivate Event
Prevent User from Closing Reminder Form
Close Button Property
Buttons to Move Reminders +5 min, +1 hour, +1 day
Move Reminder to Tomorrow at 9am
Docmd.GotoControl to Move to Next Record
Hide Closed Reminders

08. How Many X Days in Interval (32:00)
How many Mondays are in This Month
Combo Box with Weekdays
Separate Month, Day, Year Values
DateSerial Function to Build Dates
First Day of Month
Last Day of Month
Loop Through Days
Code Breakpoints
CLng Convert Text to Long Int
Convert Code to a Function
Specify our own Interval (month, quarter, year)
How Many Tuesdays This Year
How Many Fridays This Quarter
Select Case Statement
Count from Target Date until End of Period

09. Calculating Holidays (34:33)
United States Holidays
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

10. Recurring Appointments 1 (47:09)
Add Frequency and Recurring to Reminder Table
Days, Weeks, Months, Years
Last Completed Date
Done Button
Move Recurring Appointments Ahead
Close Non-Recurring Appointments
Filter List of Appointments by Dates
Custom SQL Recordsource
Add Where Conditions
Show Closed, Open, Both
AfterUpdate Event

11. Recurring Appointments 2 (15:33)
Add Custom Sort Order
Custom Date Range Buttons
Back and Forward One Week's Appointments
See All Appointments Not Closed

12. Ordinal Dates (3:47)
MyOrdinal Function
Display 1st, 2nd, 3rd, 4th, etc.

13. Review (6:59)




You may want to read these articles from the 599CD News:


Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
My Account
My Courses
Lost Password
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Customer Support
Web Site Tour
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Contact Info
Support Policy
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn