MS Access - Calculating holidays between dates

[Kevinski]

Hello

I am trying to find the number of holidays between any two given days using the following code, but it is calculating the holidays incorrectly (always less than the days available in the table "Holidays"). When I am using the start date as 1/1/2006 and end date as 31/1/2006,the result comes to be 10days instead of 12days.

My code

Private Function KPDays(x As Date, y As Date) As Integer
Dim i As Variant

j = 0
For i = x To y Step 1
k = "# " & i & "#"
If Weekday(i, vbSunday) = 1 Then
j = j + 1
ElseIf Weekday(i, vbSunday) = 7 Then
j = j + 1

ElseIf DLookup("[HolidayDate]", "Holidays", "[HolidayDate]="
& k) = i Then
j = j + 1
End If
Next
KPDays = j
End Function
______________________________________________________

The table 'Holidays'

Monday, 2 January 2006
Tuesday, 3 January 2006
Monday, 30 January 2006
Monday, 6 February 2006
Friday, 14 April 2006
Monday, 17 April 2006
Tuesday, 18 April 2006
Tuesday, 25 April 2006
Monday, 5 June 2006
Monday, 23 October 2006
Monday, 25 December 2006
Tuesday, 26 December 2006

Any help appreciated

 

[PHV]

you may try this:
ElseIf Not IsNull(DLookup("HolidayDate", "Holidays", "HolidayDate="
& k)) Then


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

 

[Kevinski]

That's helped thanks