Free Lessons
Courses
Seminars
TechHelp
Fast Tips
Templates
Topic Index
Forum
ABCD
 
Home   Courses   Seminars   Templates   TechHelp   Fast Tips   Forums   Help   Contact   Join   Order   Logon  
 
Home > Courses > Access > Seminars > DateTime >
 
Access DateTime Seminar

Work with Advanced Date & Time Calculations  


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

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.

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.

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

Keywords

microsoft access work days, week days, holidays, time stamp, reminder popup notification, days per month, recurring appointments, reminder popup

 

Start a NEW Conversation
 
Only students may post right now. Click here for more information on how you can set up an account. If you are a student, please LOG ON first.
 
Subscribe
 

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

6/30/2022Sales Chart
6/27/2022Rounding Errors
6/24/2022Multi Combo One Field
6/23/2022Modal & Popup
6/21/2022Week of Year
6/16/2022Value List Combo
6/15/2022Weekday
6/14/2022Project Budgets
6/14/2022Find Record
6/13/2022Access Amortization Template 2.0
 

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

YouTube Channel    LinkedIn
Keywords: access seminar work days, week days, holidays, time stamp, reminder popup notification, days per month, recurring appointments, reminder popup  Page Tag: whatsnew  PermaLink  Microsoft Access DateTime Seminar