ACC2000: How to Determine If a Date Falls on a Weekend or Holiday

ID: Q210064

The information in this article applies to:

Microsoft Access 2000

Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

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, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

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. 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: Description
     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:


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

Creating 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

   ' 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#)

True

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

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:

Q208786 ACC2000: DLookup() Usage, Examples, and Troubleshooting

Additional query words: inf

Keywords : kbusage kbdta
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbhowto