Microsoft KB Archive/198505

= ACC2000: DateAdd "w" Interval Does Not Work as Expected =

Article ID: 198505

Article Last Modified on 6/23/2005

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q198505



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



SYMPTOMS
When you use the "w" interval (which includes all the days of the week, Sunday through Saturday) to add days to a date, the DateAdd function adds the total number of days that you specified to the date, instead of adding just the number of workdays (Monday through Friday) to the date, as you might expect.

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.



CAUSE
The intervals in the DateAdd function are the same as those used in the DatePart function. The DatePart function uses the "w" interval to return the weekday, or day of the week, from a date. The value returned from the DatePart function is an integer from 1 to 7, representing the days of the week (Sunday through Saturday). When you add days using the "w" interval, weekdays (which include all the days of the week) are added.

To add workdays (Monday through Friday) to a date, use the sample function described in the "Resolution" section.



RESOLUTION
You can use the following sample user-defined function to add workdays, rather than weekdays, to a date: '********************************************************** 'Declarations section of the module '**********************************************************

Option Explicit

'========================================================== ' The DateAddW function provides a workday substitute ' for DateAdd("w", number, date). This function performs ' error checking and ignores fractional Interval values. '========================================================== Function DateAddW (ByVal TheDate, ByVal Interval)

Dim Weeks As Long, OddDays As Long, Temp As String

If VarType(TheDate) <> 7 Or VarType(Interval) < 2 Or _ VarType(Interval) > 5 Then DateAddW = TheDate ElseIf Interval = 0 Then DateAddW = TheDate ElseIf Interval > 0 Then Interval = Int(Interval)

' Make sure TheDate is a workday (round down).

Temp = Format(TheDate, "ddd") If Temp = "Sun" Then TheDate = TheDate - 2 ElseIf Temp = "Sat" Then TheDate = TheDate - 1 End If

' Calculate Weeks and OddDays.

Weeks = Int(Interval / 5) OddDays = Interval - (Weeks * 5) TheDate = TheDate + (Weeks * 7)

' Take OddDays weekend into account.

If (DatePart("w", TheDate) + OddDays) > 6 Then TheDate = TheDate + OddDays + 2 Else TheDate = TheDate + OddDays End If

DateAddW = TheDate Else                        ' Interval is < 0 Interval = Int(-Interval) ' Make positive & subtract later.

' Make sure TheDate is a workday (round up).

Temp = Format(TheDate, "ddd") If Temp = "Sun" Then TheDate = TheDate + 1 ElseIf Temp = "Sat" Then TheDate = TheDate + 2 End If

' Calculate Weeks and OddDays.

Weeks = Int(Interval / 5) OddDays = Interval - (Weeks * 5) TheDate = TheDate - (Weeks * 7)

' Take OddDays weekend into account.

If (DatePart("w", TheDate) - OddDays) > 2 Then TheDate = TheDate - OddDays - 2 Else TheDate = TheDate - OddDays End If

DateAddW = TheDate End If

End Function

How to Use the DateAddW Function
Use the DateAddW function wherever you would use the DateAdd function. For example, instead of   DateAdd("w",[StartDate],10) use:   DateAddW([StartDate],10) To test the DateAddW function, enter the following line in the Debug window, and then press ENTER: ? DateAddW(#2/2/99#,10) Note that the result is 2/16/99 (10 workdays).



Steps to Reproduce Behavior
Enter the following line in the Debug window, and then press ENTER: ? DateAdd("w",#2/2/99#,10) Note that the result is 2/12/99 (10 days), not 2/16/99 (10 workdays). Note also that the 2/12/99 (10 days) result is correct because the "w" interval includes all the days of the week, Sunday through Saturday.

