Microsoft KB Archive/115489

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

Article ID: 115489

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q115489



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.

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 the "Building Applications" manual.

NOTE: Visual Basic for Applications (used in Microsoft Access version 7.0) is called Access Basic in earlier versions. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x, or the "Building Applications" manual in version 2.0.



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 just the 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 just the workdays (Monday through Friday), rather than weekdays (Sunday through Saturday), to a date.

NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic. '**********************************************************  '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 (or Immediate window in versions 1.x and 2.0), and then press ENTER:

? DateAddW(#2/2/93#,10)

Note that the result is 2/16/93 (10 workdays).



STATUS
This behavior is by design.



Steps to Reproduce Behavior
Type the following line in the Debug window, (or Immediate window) and then press ENTER:

? DateAdd("w",#2/2/93#,10)

Note that the result is 2/12/93 (10 days), not 2/16/93 (10 workdays). Note also that the 2/12/93 (10 days) result is correct because the "w" interval includes all the days of the week, Sunday through Saturday.

