Moderate: Requires basic macro, coding, and interoperability skills.
This article applies only to a Microsoft Access database (.mdb).
For a Microsoft Access 2002 version of this article, see
290152.
IN THIS TASK
SUMMARY
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.
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.
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.
back to the top
Create a Holidays Table
The following example requires a table with a particular structure
for storing Holiday dates. To create the table and sample records, follow these steps:
- Create a new table in Design view and add the following fields:
Table: Holidays
-----------------------
Field Name: HolidayName
Data Type: Text
Field Name: HoliDate
Date Type: Date/Time
- Save the table as Holidays and switch the table to Datasheet view. Add the following records:
HolidayName HoliDate
--------------------------------------------
New Year's Day 1/1/2000
Martin Luther King, Jr. Day (USA) 1/15/2000
Memorial Day (observed-USA) 5/29/2000
Labor Day (USA) 9/4/2000
- Close and save the Holidays table.
back to the top
Create the Custom Function
To create a function that determines if a date falls on a weekend or
holiday, follow these steps:
- Create a new module in Design view.
- Add the following function:
Function OfficeClosed(TheDate) As Integer
OfficeClosed = False
TheDate = Format(TheDate, "dd/mm/yyyy")
' 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
- To test this function, type the following line into the Immediate window, and then press ENTER:
? OfficeClosed(#9/4/2000#)
Note that this returns a True value (-1) because 9/4/2000 is listed in the Holidays table.
back to the top
Usage Example
You can use the custom OfficeClosed() function to calculate due dates. For
example, if your office or business is closed for a three-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
back to the top
REFERENCES
For more information about the Weekday() function, in the Visual Basic Editor, click
Microsoft Visual Basic Help on the
Help menu, type
weekday function in the Office Assistant or the Answer Wizard, and then click
Search to view the topic.
For additional information about the
DLookup() function, please click the article number below to view the article in the Microsoft Knowledge Base:
208786 ACC2000: DLookup() Usage, Examples, and Troubleshooting
back to the top