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.

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.

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)

|