ACC: DateAdd() "w" Interval Does Not Work as Expected

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0

SYMPTOMS

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

When you add days to a date using the "w" interval, the DateAdd() function adds the total number of days you specified to the date, instead of adding the appropriate number of workdays to the date as you 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 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.

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)

   DateAddW([StartDate],10)

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

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

Enter the following line in the Debug window, (or Immediate window) and then press ENTER:

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

REFERENCES

For more information about the DateAdd() function, search for "DateAdd" using the Microsoft Access 7.0 Help Index.

Keywords          : kbprg PgmOthr
Version           : 1.0 1.1 2.0 7.0
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbprb
Solution Type     : kbcode