DateDiff Working Days only not including holidays code problem

[Kevin]

Hi,

I have found this code to work out date difference (working days only) and
to include holidays from my [tblHoliDate] table the code seems to work if
only one date in my holiday table falls between the [StartDate] and
[Enddate], but if there is more than one holiday date between [StartDate] and
[EndDate] it only seems to take off the first. (I hope this makes sense!).

Could any tell me where I am going wrong Please?



Public Function CountWorkingDays(StartDate As Date, EndDate As Date) As
Integer

On Error GoTo ErrorHandler
'Get the number of workdays between the given dates
'function uses the Holidays table and deducts them from the days to allow
'for weekends
'and holidays when calculating deadline dates

Dim dbs As Database
Dim rstHolidays As Recordset

Dim lngIndex As Long
'Dim MyDate As Date
Dim lngNumDays As Long
Dim strCriteria As String
Dim NumSgn As String * 1

Set dbs = CurrentDb
Set rstHolidays = dbs.OpenRecordset("Holidays", dbOpenDynaset)

NumSgn = Chr(35)

StartDate = Format(StartDate, "Short Date")

For lngIndex = CLng(StartDate) To CLng(EndDate)
Select Case (Weekday(StartDate))
Case Is = 1
'Do Nothing, it is Sunday
Case Is = 7
'Do Nothing, it is Saturday
Case Else 'Normal Workday
strCriteria = "[HoliDate] = " & NumSgn & StartDate & NumSgn
rstHolidays.FindFirst strCriteria
If (rstHolidays.NoMatch) Then
lngNumDays = lngNumDays + 1
Else
'Do Nothing, it is NOT a Workday
End If
End Select
StartDate = DateAdd("d", 1, StartDate)
Next lngIndex

CountWorkingDays = lngNumDays
Exit Function
ErrorHandler:
'on error destroy objects and exit function... The function will return
a zero value
Set rstHolidays = Nothing
Set dbs = Nothing
Exit Function
End Function

 

[Kevin]

Thank You works a treat

Hi Bob,
The backslashes "\" put the next character in the string absolutely. Since
the output of the Format() function is always a string, it returns Null when
the value of StartDate is null.

ruralguy,

Would you explain the formatting portion of:

If Weekday(StartDate, vbMonday) <= 5 And _
IsNull(DLookup("[Holiday]", "tblHolidays", _
"[HolDate] = " & Format(StartDate, "\#mm\/dd\/yyyy\#;;;\N\u\l\l"))

Why the back-slashes and what's #;;;\N\u\l\l"))?

Thanks and best wishes,

Bob

Kevin,
Here's the function I use. Maybe that will help.

[quoted text clipped - 44 lines]

Exit Function
End Function