Microsoft KB Archive/290152

= How to determine if a date falls on a weekend or a on holiday in Access 2002 =

Article ID: 290152

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q290152



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

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



For a Microsoft Access 97 version of this article, see 149127.

For a Microsoft Access 2000 version of this article, see 210064.

IN THIS TASK
SUMMARY
 * Creating the Holidays Table
 * Creating the Custom Function
 * Usage Example

REFERENCES



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 a Sunday. Then, it uses the DLookup function to determine if the date falls on a date that is stored in a user-created Holidays table.

back to the top

Creating the 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 then 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 then 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.

back to the top

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.</li>  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(&quot;HoliDate&quot;, &quot;Holidays&quot;, &quot;[HoliDate]=#&quot; _ & TheDate & &quot;#&quot;)) Then OfficeClosed = True End If

End Function </li>  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. </li></ol>

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. The following is 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

<div class="references_section">