Dealing with broken DateDiff("w",startdate,enddate) function (1 Viewer)

[phual]

I've fallen foul of the error in the DateDiff function (MS Access 2002) when trying to find the number of working days between two dates. I've had no end of problems working round the issue, but the last problem has stumped me.

I've implemented the MS provided workaround as detailed here:
http://support.microsoft.com/kb/288194
Essentially, this means using a UDF called DateDiffW instead of the documented DateDiff function (i.e. DateDiff("w",startdate,enddate)).

If I put the following code in the intermediate window;

?DateDiffW(#05/11/2012#,#11/11/2012#)

I get an answer of 130, when I expected an answer of 4. If I call the function from a query (inputting the same dates). I get a "Type missmatch" error.

This 130 answer suggests to me that the function is getting confused with date formats (I'm entering UK date format, not American date format). I've tried pre-formatting the date in the function (i.e. DateDiffW(format(#05/11/2012#,"mm/dd/yyyy"),format(#11/11/2012#"mm/dd/yyyy"))), but to no avail.

Suggestions?

Stuart

 

[Brianwarnock]

I'm sorry to have to tell you but the design grid is the only place you can use local date formats for date strings in Access, elsewhere you have to use US format with date strings eg #13/01/2012# would be #01/13/2012#
If the date was coming from a form field you would be ok as that date would have been stored as DD and then passed to the query.

Brian

 

[phual]

The thing is, I'm not using strings. The two dates that I'm comparing are from a table, where each field I'm compaing are in date format. The function executes happily (and delivering the correct result) for other dates (when executed via a query):

05/11/2012 to 06/11/2012 = 1
05/11/2012 to 15/11/2012 = 8
01/11/2012 to 15/11/2012 = 10

But throws an error when I enter these dates:

05/11/2012 to 11/11/2012

Stuart

 

[Brianwarnock]

This is a totally different scenario to your first post!!

What is the SQL for your query, let's start there

Brian

 

[Brianwarnock]

Interestingly none of the others start or end on a weekend. I don't know the function, it may have a fault as it may not have been tested for weekends if it is supposed to ignore them, but then I don't see why it should give the error you stated.

Brian

 

[jdraw]

I looked at the function, then tried a few things.
I added a parameter to the Function DateUK as Boolean True if UK format for Date D/M/Y.

and if True, then just reversed the M/D values.

I'm in Canada and use M/D/Y default, so there may be more at play than meets the eye.

I have copied the Function with my revised code and a test routine.
Don't know if it solves the issue, but it may be helpful.

Function DateDiffW(BegDate As Date, EndDate As Date, DateUK As Boolean) As Integer
'
'M$oft function to get number of weekdays between 2 dates
'  see  http://support.microsoft.com/kb/288194
'  and post related to issue
'    http://www.access-programmers.co.uk/forums/showthread.php?t=238589
'
    Const SUNDAY = 1
    Const SATURDAY = 7
    Dim NumWeeks As Integer
    On Error GoTo DateDiffW_Error

'  code added by jdraw to deal with DateUK parameter
    If DateUK Then    'reverse the month and day
        BegDate = DateSerial(Year(BegDate), Day(BegDate), Month(BegDate))
        EndDate = DateSerial(Year(EndDate), Day(EndDate), Month(EndDate))
        Debug.Print "US Date Format is : " & BegDate & "   " & EndDate
    End If
' end code added by jdraw

    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

    On Error GoTo 0
    Exit Function

DateDiffW_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure DateDiffW of Module Module1"
End Function

This is the test routine.

Sub test_DatediffW()
    Dim myBeg As Date
    Dim myEnd As Date
    Dim UKFormat As Boolean
    On Error GoTo test_DatediffW_Error

    UKFormat = False
    myBeg = #5/11/2010#
    myEnd = #11/11/2010#
    Debug.Print "myBeg orig  " & myBeg & "  " & "myEnd orig " & myEnd & "   DateUK is " & UKFormat

    Debug.Print "Number of working days :  " & DateDiffW(myBeg, myEnd, UKFormat)

    On Error GoTo 0
    Exit Sub

test_DatediffW_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure test_DatediffW of Module Module1"
End Sub

My test output:

myBeg orig  5/11/2010  myEnd orig 11/11/2010   DateUK is True
US Date Format is : 11/5/2010   11/11/2010
Number of working days :  4
myBeg orig  5/11/2010  myEnd orig 11/11/2010   DateUK is False
Number of working days :  132

 

[phual]

Brian,

I've not included the actual SQL due to its complexity, but this simple example shows the same result:

SELECT [tbl BaseQuery].RefID, [tbl BaseQuery].ReferralDate, [tbl BaseQuery].inPlanningDate, DateDiffW([ReferralDate],[inPlanningDate]) AS Diff
FROM [tbl BaseQuery]
WHERE ((([tbl BaseQuery].ReferralDate)>=#11/1/2012#) AND (([tbl BaseQuery].inPlanningDate) Is Not Null));

Sorry if my original post wasn't clear. A combination of typing "function" when I should have typed "query" in the last sentence probably didn't help, but I also made a leap as to the possible cause, which may not have been true (i.e. assuming that the problem is the date format).

jdraw,

Thanks for the response, but I'm not going to be able to look at it until next week now (working day ending and have to go).

Thanks for help so far - have a good weekend.

Stuart

 

[Brianwarnock]

Nothing in that query that should cause a format problem as dates are coming from table and will therefore be stored as DD .

Hope Jdraws solution works for you but it is a puzzle?

Brian

 

[phual]

jdraw,

I've done a quick test on your revised code and it's worked a treat (entering a value of 0 for the DateUK parameter)! It looks like the problem was indeed that Microsoft's documented solution failed to take account of UK date formats.

Thanks

Stuart

 

[jdraw]

I just tried a few things to see if I could get your format to work. I have not tested it to any great degree.

If you are interested in Working days, you should see this link and dialog.

http://www.utteraccess.com/forum/lofiversion/index.php/t1959936.html

Also you may see this wikipedia entry
http://en.wikipedia.org/wiki/ISO_week_date