Access 2007-2016
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 
NOTICE: I am right in the path of Hurricane Irma, please read this   dismiss
 
 

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 - 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
Excel.Application
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
Screen.ActiveForm
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
CurrentProject
AllForms
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)

 


 

 
 

Student Interaction: Access Advanced DateTime Seminar

Richard on 1/20/2015:  In the Access Advanced DateTime 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, use event timers in your database to 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. This seminar is 5 hours and 22 minutes long. Click here for more information on the Access Advanced DateTime Seminar, including a complete course outline, sample video, and lots more.
Alex Hedley on 2/9/2015: 14:00
How to use the Holidays Array in the Excel Function Networkdays with Access
Microsoft Article

SUMAILA MOHAMMED on 2/20/2015: you guys are doing a graet job.thumbs up
mohamed mater on 2/25/2015: i want to expert in access

Reply from Alex Hedley:

Well this and the other Seminars will help you get there.

Richard Lanoue on 4/30/2015: I want to open my main form, and once it's open, it opens the appointment form in minimize mode... how do I do that?

Reply from Alex Hedley:

DoCmd.Minimize

John Hubbard on 9/13/2016: Just downloaded your DateTimeSeminar database, ReminderF, on opening am receiving Run-time error "2247" invalid use of .(dot)or ! operator or invalid use of parentheses. Getting same error on ReminderPopupF.

Reply from Alex Hedley:

Have you changed around any ordering of References in the VBA Editor

Richard Wilson on 10/30/2016: I have a number of sub-forms on a form. Is there any way for a change on a sub-form to trigger a "LastUpdated" field on the master form? Frankly, I consider them all part of the form, but because of many-to-many relationships, I find it necessary to use a plethora of sub-forms.

Reply from Alex Hedley:

Take a look at the HasModule = False on the Form.

You could call a Function:
Me.Parent.<Name Of Function>
or
Forms("NameOfParent").<Name Of Function>

Jim Ogier on 1/12/2017: How can I default a Date/time field to a PM time value?
99 percent of the date time entries are a PM time and it would save much time to default to PM.

Thanks
Jim O

Reply from Alex Hedley:

=Now()
Or =Date() + x amount of hours to make it into a PM value.

Jim Ogier on 1/19/2017: It would be nice to be able to have some records added to the table as a new record, i.e. click a button and the appointment is moved to the bottom of the list as a new record.

Thanks
Jim O

Reply from Alex Hedley:

Can you give more of an example, I'm not sure what you want.

The order in which you have set the continuous form will display so if it's date order and you add a later date it will add to the bottom.

John H on 4/18/2017: Just downloaded your database again on different computer. Opening up ReminderF throws same error with debug line
W = W & "ReminderDateTime < #" & EndDate + 1 & "#"

Nothing has been changed. Please advise Thanks

John H on 4/19/2017: Reference library showed missing: ms outlook 15.0 object library. Removed check and now your db ReminderF opens without a hitch.
MUBEEZI MICAH on 6/27/2017: Many thanx. I liked this lesson
 

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

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP