ACC: How to Determine If a Date Falls on a Weekend or Holiday (149127)

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.

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:

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/96
      Martin Luther King, Jr. Day (USA)   1/15/96
      Memorial Day (observed-USA)         5/27/96
      Labor Day (USA)                     9/2/96

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