Microsoft KB Archive/207795

= ACC2000: "W" Option of the DateDiff Function Does Not Work =

Article ID: 207795

Article Last Modified on 6/23/2005

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q207795



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

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

For a Microsoft Access 2002 version of this article, see 288194.



SYMPTOMS
When you use the "w" 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 "w" option is supposed to work the same way 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.

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. 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]) 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 Immediate Window, type the following line, and then press ENTER: ?DateDiffW(#2/1/99#,#2/15/99#) Note that 10 is returned, the number of work days.



Steps to Reproduce Behavior
In the Immediate Window, type the following line, and then press ENTER: ? DateDiff("W",#2/1/99#,#2/15/99#) Note that 2 is returned (the number of weeks), not 14 (the number of days) or 10 (the number of work days).

