Business Days Function needs error handling

[woro2006]

I am hoping that someone can edit the following code to handle

1) When either of the input (i.e. dtstartdate or dtendate) is null, leave the result null
2) When dtstartdate > dtendate, networkdays=0
3) If there are any other errors, just ignore them (i.e. I don't want them to display #error on my query result set)

Thanks in advance.

I found this link in a forum, which gives me the following code.

Code:


Public Function fNetWorkdays(dtStartDate As Date, dtEndDate As Date) As Long
'Returns the number of workdays between the two passed dates. Saturdays and
'Sundays are NOT considered workdays. Plus there is an assumption that a
'table exists that is named tblHolidays that identifies EACH holiday date
'in a field named HolidayDate. Also, the FIRST date of the range is INCLUDED
'as a work day, unless its a Saturday or Sunday.
'''''''''''''''''''''''''
'Author: Brent Spaulding
'Version: 2
'Date: 1/11/2007
''''''''''''''''''''

Dim lngDays As Long
Dim lngSaturdays As Long
Dim lngSundays As Long
Dim lngHolidays As Long
Dim lngAdjustment As Long

'Count the number of RAW days between the dates
lngDays = DateDiff("d", dtStartDate, dtEndDate)

'Count the number of Sundays between the two days. Note the use of "ww" as
'the date interval which forces the count of sundays
lngSundays = DateDiff("ww", dtStartDate, dtEndDate, vbSunday)

'Count the number of Saturdays between the two dates. Note the use of "w" as
'the date interval which will count the <day of first date in DateDiff()>.
'So, to count the Saturdays, I adjust the start date of the datediff function
'to the saturday BEFORE the dtStartDate of the passed range, thus the number
'of Saturdays between the passed range is returned.
lngSaturdays = DateDiff("w", IIf(Weekday(dtStartDate, vbSunday) = vbSaturday, _
dtStartDate, _
dtStartDate - Weekday(dtStartDate, vbSunday)), _
dtEndDate)

'Count the number of holidays
lngHolidays = Nz(DCount("*", "tblHolidays", _
"HolidayDate Between #" & dtStartDate & _
"# And #" & dtEndDate & "#"), 0)

'Make an adjustment based on whether or not the start date is a Saturday
If Weekday(dtStartDate, vbSunday) = vbSunday Or Weekday(dtStartDate, vbSunday) = vbSaturday Then
lngAdjustment = 0
Else
lngAdjustment = 1
End If

'Return the result
fNetWorkdays = lngDays - lngSundays - lngSaturdays - lngHolidays + lngAdjustment

End Function

 

[PaulBricker]

One way to deal with this would be to handle the error before it goes to the function. In your query, you can use conditonal statements to test for any of the conditions except maybe the last one.

netWorkDays:IIf(IsNull(dtstartDate) or IsNull(dtendDate),"",IIf(dtstartDate > dtendDate,0,fNetWorkdays(dtStartDate, dtEndDate)))

Paul

 

[MichaelRed]

while PaulBricker's suggention is quite reasonable, there are similar procedures posted here (in Tek-Tips, faqs) which may have more robust error handling. On the other hand, errors in your queries may provide valuable information regarding poor data grooming or validation in your overall application, so the 'disabling' of errors in favor of simple ignorance (or the effors) is not usually the better course of action.




MichaelRed

 

[PHV]

Replace thi:
Public Function fNetWorkdays(dtStartDate As Date, dtEndDate As Date) As Long
with this:
Public Function fNetWorkdays(dtStartDate, dtEndDate) As Variant
If Not (IsDate(dtStartDate) And IsDate(dtEndDate) Then Exit Function
If dtStartDate > dtEndDate Then
fNetWorkdays = 0
Exit Function
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886