469,277 Members | 2,370 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,277 developers. It's quick & easy.

FindFirst working intermittently.

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(StartDate 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_WorkingDays2

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

Set DB = CurrentDb
Set rst = DB.OpenRecordset("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(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then


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

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

'*********** Code End **************
May 31 '07 #1
2 1499
bartonc
6,596 Expert 4TB
From Member Introductions.
May 31 '07 #2
MMcCarthy
14,534 Expert Mod 8TB
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer
  3. '....................................................................
  4. ' Name: WorkingDays2
  5. ' Inputs: StartDate As Date
  6. ' EndDate As Date
  7. ' Returns: Integer
  8. ' Comment: Accepts two dates and returns the number of weekdays between them
  9. ' Note that this function has been modified to account for holidays. It requires a table
  10. ' named tblHolidays with a field named HolidayDate.
  11. '....................................................................
  12. On Error GoTo Err_WorkingDays2
  13. Dim intCount As Integer
  14. Dim rst As DAO.Recordset
  15. Dim DB As DAO.Database
  16.     Set DB = CurrentDb
  17.     Set rst = DB.OpenRecordset("SELECT Format([HolidayDate], 'dd/mm/yyyy') As HDate FROM tblHolidays", dbOpenSnapshot)
  18.  
  19.     StartDate = Format(StartDate, "dd/mm/yyyy")
  20.     EndDate = Format(EndDate, "dd/mm/yyyy")
  21.     'StartDate = StartDate + 1
  22.     'To count StartDate as the 1st day comment out the line above
  23.     intCount = 0
  24.  
  25.     Do While StartDate <= EndDate
  26.         rst.FindFirst "[HDate] = #" & StartDate & "#"
  27.         If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then
  28.             If rst.NoMatch Then intCount = intCount + 1
  29.         End If
  30.         StartDate = StartDate + 1
  31.     Loop
  32.  
  33.     WorkingDays2 = intCount
  34.  
  35. Exit_WorkingDays2:
  36.     Exit Function
  37.  
  38. Err_WorkingDays2:
  39.     Select Case Err
  40.     Case Else
  41.  
  42.     MsgBox Err.Description
  43.     Resume Exit_WorkingDays2
  44.     End Select
  45. End Function
  46.  
Jun 1 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by Paul | last post: by
4 posts views Thread by jon.lewis | last post: by
2 posts views Thread by keri | last post: by
25 posts views Thread by Rick Collard | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.