DateDiff query not returning correct results

Autoeng

Why me?
Local time
Today, 17:36
Joined
Aug 13, 2002
Messages
1,302
I have a query to calculate DateDiff that is not returning the correct result. Can anybody see what is incorrect here? We do not work on Fridays, Saturdays or Sundays.

Thanks to pdx_man for previously posting the example that I based my query on.

DateDiff("d",[RelDate],[ImpDate],2) AS DaysDiff, Int([daysdiff]/7) AS Weeks, [daysdiff]-[weeks]*3+IIf(Weekday([ImpDate],2)>5,5-Weekday([ImpDate],2),0)+IIf(Weekday([RelDate],2)=6,1,0)+1 AS weekdays

For example if RelDate was 6/14/02 and ImpDate was 7/22/02 the weekdays return should be 21 but I am getting 24.

Autoeng
 
Last edited:
'Nother modification:

Code:
Public Function RetWeekDays(varStart As Date, varEnd As Date) As Long
Dim lngStart As Long
Dim lngEnd As Long
Dim lngDiff As Long
Dim lngCounter As Long
Dim lngChkDate As Long
Dim lngDayCount As Long

    If varStart > 0 And varEnd > 0 Then
        lngStart = Format(varStart, "#")
        lngEnd = Format(varEnd, "#")

        lngDiff = Abs(lngEnd - lngStart)

        For lngCounter = 1 To lngDiff
            lngChkDate = Format(lngStart + lngCounter, "w")
            If (lngChkDate = 2) Or (lngChkDate = 3) Or (lngChkDate = 4) Or (lngChkDate = 5) Then
                lngDayCount = lngDayCount + 1
            End If
        Next

        Else
        lngDayCount = 0
    End If
    
    RetWeekDays = lngDayCount

End Function
 
But check your code against what I originally posted. I put your dates in the original function and I get 21 days. Here is the query that I just used (With the reference to the above function):

SELECT Table2.Date1, Table2.Date2, DateDiff("d",[date1],[date2],2) AS DaysDiff, Int([daysdiff]/7) AS Weeks, [daysdiff]-[weeks]*2+IIf(Weekday([date2],2)>5,5-Weekday([date2],2),0)+IIf(Weekday([date1],2)=6,1,0)-IIf(Weekday([date1],2)>Weekday([date2],2),2,0) AS weekdays, RetWeekDays([date1],[date2]) AS ProgNum
FROM Table2;

I just looked at your code a little closer and found the difference
 
Last edited:
Give this a try. It's not the fastest in the world but is a little
more generic since it allows the user to specify any weekdays
to exclude without hard-wired code to handle each weekday in
turn.

Also included is a neat timer routine that I found somewhere (?)-
sorry, I'd be happy to apply credit if only I knew the source. You can
use it with various similar routines to determine comparative processing
speeds, and it doesn't rely on Access' timer functions.

Code:
Option Compare Database
Option Explicit

Private Declare Function a2Ku_apigettime Lib "winmm.dll" _
Alias "timeGetTime" () As Long
Dim lngstartingtime As Long

Sub a2kuStartClock()
    lngstartingtime = a2Ku_apigettime()
End Sub

Function a2kuEndClock()
    a2kuEndClock = a2Ku_apigettime() - lngstartingtime
End Function

'*******************************************************************
 Function CountWkDays(varstart As Variant, varend As Variant, _
incl As Boolean, ExclDays As String) As Integer
'*******************************************
'Name:      CounttWkDays (Function)
'Purpose:   Simple, non-formula method of
'           counting weekdays from a given
'           date
'Inputs:    ? countwkdays(#6/14/02#, #7/22/02#, False, "167")
'Output:    21
'Note:      if incl = true then include varstart in the
'           calculation, otherwise don't
'*******************************************
Dim thedate As Date, n As Integer, i As Integer
Dim myStartDte As Date
'Start the clock
a2kuStartClock

thedate = DateValue(varstart)
myStartDte = DateValue(varstart)
varend = DateValue(varend)
n = 0
Do While thedate <= varend
   If InStr(ExclDays, LTrim(Str(WeekDay(thedate, 1)))) = 0 Then
      n = n + 1
   End If
   thedate = thedate + 1
Loop
'Stop the clock and print the results to the debug window
Debug.Print "CountWkDays" & " executed in: " & a2kuEndClock & _
" milliseconds"

CountWkDays = n + IIf(incl = True And _
InStr(ExclDays, LTrim(Str(WeekDay(myStartDte, 1)))) = 0, 1, 0)

End Function
 
Thank you gentlemen. pdx_man I apologize for screwing up your SQL. I guess when I was removing your testers I screwed it up.

Again thanks to both of you,

Autoeng
 
pdx_man:

If I have a start and stop date that are the same (i.e. 7/16/02 and 7/16/02) the query responds correctly with a 0. However I would like to force it to a 1 without adding 1 to all of the others. Is there a way to do so?

Autoeng
 
Query:

)-IIf(Weekday([date1],2)>Weekday([date2],2),2,0) + IIf([date1] = [date2],1,0) AS weekdays

Code:

lngDiff = Abs(lngEnd - lngStart)
If lngDiff = 0 Then lngDiff = 1
 

Users who are viewing this thread

Back
Top Bottom