FindFirst working intermittently.

[JasCot75]

Hello all, my first go at this so please be gentle.

I'm putting together a simple access database and have run into a problem with date function I found on the web. The code appears to work, but for some reason early dates in May 2007 although present in the holidays table are not being found by the find first statement in the code. For example bank holiday 07/05/2007 is being classed as working day, but 28/05/2007 is not despite both being in the table.

Any ideas? Function code below

Public Function WorkingDays2(St artDate As Date, EndDate As Date) As Integer
'.............. ............... ............... ............... .........
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function has been modified to account for holidays. It requires a table
' named tblHolidays with a field named HolidayDate.
'.............. ............... ............... ............... .........
On Error GoTo Err_WorkingDays 2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordse t("SELECT [HolidayDate] FROM tblHolidays", dbOpenSnapshot)

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolidayDate] = #" & StartDate &"#"
If Weekday(StartDa te) <> vbSunday And Weekday(StartDa te) <> vbSaturday Then


If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDay s2:
Exit Function

Err_WorkingDays 2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDay s2
End Select

End Function

'*********** Code End **************

[bartonc]

From Member Introductions.

[MMcCarthy]

The problem is probably one of formatting. Format your dates to the required mm/dd/yyyy or dd/mm/yyyy to get the correct results.

[code=vb]
Public Function WorkingDays2(St artDate As Date, EndDate As Date) As Integer
'.............. ............... ............... ............... .........
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function has been modified to account for holidays. It requires a table
' named tblHolidays with a field named HolidayDate.
'.............. ............... ............... ............... .........
On Error GoTo Err_WorkingDays 2
Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database
Set DB = CurrentDb
Set rst = DB.OpenRecordse t("SELECT Format([HolidayDate], 'dd/mm/yyyy') As HDate FROM tblHolidays", dbOpenSnapshot)

StartDate = Format(StartDat e, "dd/mm/yyyy")
EndDate = Format(EndDate, "dd/mm/yyyy")
'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above
intCount = 0

Do While StartDate <= EndDate
rst.FindFirst "[HDate] = #" & StartDate & "#"
If Weekday(StartDa te) <> vbSunday And Weekday(StartDa te) <> vbSaturday Then
If rst.NoMatch Then intCount = intCount + 1
End If
StartDate = StartDate + 1
Loop

WorkingDays2 = intCount

Exit_WorkingDay s2:
Exit Function

Err_WorkingDays 2:
Select Case Err
Case Else

MsgBox Err.Description
Resume Exit_WorkingDay s2
End Select
End Function
[/CODE]