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

Thank You works a treat
"ruralguy via AccessMonster.com" wrote:

> 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.
>
> raskew wrote:
> >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
>
> --
> RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
> Please post back to this forum so all may benefit.
>
> Message posted via http://www.accessmonster.com
>
>