Microsoft KB Archive/95977

= ACC: &quot;W&quot; Option of the DateDiff Function Does Not Work =

Article ID: 95977

Article Last Modified on 1/18/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 Q95977



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



SYMPTOMS
When you use the &quot;w&quot; Weekday option to calculate the number of weekdays between two dates, the DateDiff function returns the number of weeks, not the number of work days. The &quot;w&quot; option is supposed to function the same as &quot;d&quot; 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 &quot;d&quot; for &quot;w&quot;. 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 &quot;Building Applications with Microsoft Access&quot; 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 &quot;Introduction to Programming&quot; manual in Microsoft Access version 1.x or the &quot;Building Applications&quot; 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(&quot;ww&quot;, 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(&quot;W&quot;,[StartDate],[EndDate])

use the following:

DateDiffW([StartDate],[EndDate])

NOTE: This function returns the days UP TO the ending date, not UP TO and INCLUDING the ending date.

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#)

Note that 11 is returned, the number of work days.



Steps to Reproduce Behavior
In the Debug Window, type the following line, and then press ENTER:

? DateDiff(&quot;W&quot;,#2/2/97#,#2/18/97#)

Note that 2 is returned (the number of weeks), not 16 (the number of days) or 11 (the number of work days).

