ACC: "W" Option of the DateDiff() Function Does Not Work
ID: Q95977
|
The information in this article applies to:
-
Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97
SYMPTOMS
Moderate: Requires basic macro, coding, and interoperability skills.
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 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])
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.
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#)
Note that 2 is returned (the number of weeks), not 16 (the number of days)
or 11 (the number of work days).
REFERENCES
For more information about the DateDiff function, search the Help Index
for "DateDiff," or ask the Microsoft Access 97 Office Assistant.
Additional query words:
dates work day diff part
Keywords : kbprg
Version : WINDOWS:1.0,1.1,2.0,7.0,97
Platform : WINDOWS
Issue type : kbprb