ACC: DateDiff() Function's "W" Option Does Not Work

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SYMPTOMS

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

When calculating the number of weekdays between two dates using the "w" Weekday option, the DateDiff() function returns the number of weeks, not the number of work days. The "w" option is supposed to function the same as "d" for DateDiff(). It is provided as an option for compatibility with the DatePart() function.

RESOLUTION

If you are using the DateDiff() function to return the number of days, substitute "d" for "w". You can use the Visual Basic code in this article to return the number of work days rather than the number of days.

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.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. 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 Microsoft Access version 2.0

The following code provides a function, DateDiffW(), that calculates the number of work days between two dates:

   Function DateDiffW(BegDate, EndDate)
      Const SUNDAY = 1
      Const SATURDAY = 7
      Dim NumWeeks As Integer

      If BegDate > EndDate Then
         DateDiffW= 0
      Else
         Select Case Weekday(BegDate)
            Case SUNDAY : BegDate = BegDate + 1
            Case SATURDAY : BegDate = BegDate + 2
         End Select
         Select Case Weekday(EndDate)
            Case SUNDAY : EndDate = EndDate - 2
            Case SATURDAY : EndDate = EndDate - 1
         End Select
         NumWeeks = DateDiff("ww", BegDate, EndDate)
         DateDiffW= NumWeeks * 5 + Weekday(EndDate) - Weekday(BegDate)
      End If
   End Function

How to Use the DateDiffW() Function

Use the DateDiffW() function wherever you would use DateDiff(). Instead of

   DateDiff("W",[StartDate],[EndDate])

   DateDiffW([StartDate],[EndDate])

Steps to Test the DateDiffW() Function

In the Debug Window (or Immediate window in versions 1.x and 2.0), type the following line, and then press ENTER:

   ?DateDiffW(#2/2/97#,#2/18/97#)

MORE INFORMATION

Steps to Reproduce Behavior

In the Debug Window, type the following line, and then press ENTER:

   ? DateDiff("W",#2/2/97#,#2/18/97#)

REFERENCES

For more information about the DateDiff function, search the Help Index for "DateDiff," or ask the Microsoft Access 97 Office Assistant.