ACC: How to Determine If a Date Falls on a Weekend or Holiday
The information in this article applies to:
- Microsoft Access version 7.0, 97
SUMMARY
Moderate: Requires basic macro, coding, and interoperability skills.
This article shows you how to create a Visual Basic for Applications
function to determine if a date falls on a weekend or holiday. This example
is useful for setting due dates in applications that have billing or invoicing features.
This article assumes that you are familiar with Visual Basic for
Applications and with creating Microsoft Access applications using the
programming tools provided with Microsoft Access. For more information
about Visual Basic for Applications, please refer to your version of the
"Building Applications with Microsoft Access" manual.
MORE INFORMATION
The following example uses the WeekDay() function to determine if
a specific date falls on a Saturday or Sunday. Then, it uses a DLookup()
function to determine if it falls on a date stored in a user-created
Holidays table.
Creating a Holidays Table
The following example requires a table with a particular structure
for storing Holiday dates. You can follow these steps for creating the
table and sample records:
1. Create a new table in Design view and add the following fields:
Table: Holidays
-----------------------
Field Name: Description
Data Type: Text
Field Name: HoliDate
Date Type: Date/Time
2. Save the table as Holidays and switch the table to Datasheet view. Add
the following records:
Description HoliDate
--------------------------------------------
New Year's Day 1/1/96
Martin Luther King, Jr. Day (USA) 1/15/96
Memorial Day (observed-USA) 5/27/96
Labor Day (USA) 9/2/96
3. Close and save the Holidays table.
Creating the Custom Function
To create a function that determines if a date falls on a weekend or
holiday, follow these steps:
1. Create a new module in Design view.
2. Add the following function:
Function OfficeClosed(TheDate) As Integer
OfficeClosed = False
' Test for Saturday or Sunday.
If WeekDay(TheDate) = 1 Or WeekDay(TheDate) = 7 Then
OfficeClosed = True
' Test for Holiday.
ElseIf Not IsNull(DLookup("HoliDate", "Holidays", "[HoliDate]=#" _
& TheDate & "#")) Then
OfficeClosed = True
End If
End Function
3. To test this function, type the following line into the Debug window,
and then press ENTER:
? OfficeClosed(#9/2/96#)
Note that this returns a True value (-1) because 9/2/96 is listed in
the Holidays table.
Usage Example
You can use the custom OfficeClosed() function to calculate due dates. For
example, if your office or business is closed for a 3-day weekend, you may
want to extend your customers' grace period for their outstanding bills.
Here's sample code for adding one more day to a grace period:
DueDate=OrderDate+30
Do While OfficeClosed(DueDate)
DueDate=DateDue+1
Loop
REFERENCES
For more information about the Weekday() function, search the Help Index
for "weekday," or ask the Microsoft Access 97 Office Assistant.
For more information about the DLookup() function, please see the
following article in the Microsoft Knowledge Base:
ARTICLE-ID: Q108098
TITLE : ACC1x: DLookup() Usage, Examples, and Troubleshooting Tips
Additional query words:
Keywords : kbusage
Version : 7.0 97
Platform : WINDOWS
Hardware : x86
Issue type : kbhowto