Workdays And Holidays

[errolf]

I am using the the following Function to calculate Working Days. I have a table of Holidays with 2 Fields Holidate(Field Type Date) Name (Field Type Text) The Code works fine when the day in the Holidate field of the table has more than 1 digit but it will not work when it is a single digit date below 10 I am using "dd-mm-yy" format.
I have tried changing the format to "mm-dd-yy" but the function still does not recognise single digit days.
08/24/06 will work but 08/08/06 will not work.
Using the Dcount function finds all the dates in the Holidays Table.
Code Below
Regards Errol

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

On Error GoTo Err_WorkDayHol
Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HoliDate] FROM tblHolidays", dbOpenSnapshot)

'
intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HoliDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkDayHol = intCount

Exit_WorkDayHol:
Exit Function

Err_WorkDayHol:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkDayHol
End Select

End Function

 

[Ed2020]

Replace:

rst.FindFirst "[HoliDate] = #" & StartDate & "#"

With:

rst.FindFirst "[HoliDate] = #" & Format(StartDate,"mm/dd/yyyy") & "#"

Ed Metcalfe.


Please do not feed the trolls.....