Calculating working days (NetWorkdays) not working in Access 2

[HacH]

Hi,

I'm having an issue calculating networkdays in Access 2007. I've read a number of threads on this forum and the VB forum and tried the sample code but cannot seem to deduct the holidays from the given dates.

Please find link below to my sample Access 2007 database that I've created with 2 tables (Employees, Holidays) and a query for subtracting the dates, which uses the function with the code. Given that the StartDate is 01/09/2010 and EndDate is 30/09/2010, I would expect the function to return '14' in the SickDays field in Query1, as 01/09/2010 to 10/09/2010 are set as holidays in the 'Holidays' table and after taking off the weekend days, you should be left with only 14 days.

Link to access database:

http://www.mediafire.com/?hlkdv0ipxkkbjca

I have also tried a few examples off the Internet and am begining to think whether I'm doing something wrong with the way I've created the date fields in my database?

Thank you for your help in advance.

Hach

 

[dhookom]

Many of us can't or won't download and open files. It looks like you have non-U.S. date format which is m/d/y. Does your code correct this? If you don't bet 14 days, what do you get?

Duane
Hook'D on Access
MS Access MVP

 

[HacH]

Yes, I'm using UK date format of d/m/y. Is this the issue? If so, how can I convert dates in the code to US format prior to passing to the loop?

Sorry, I didn't realise the issue with files. Please find below details of my test database.



My test database has 2 tables and a query, as follows:

Table: Employees
Field1: EmployeeID (AutoNumber)
Field2: StartDate (Date/Time)
Field3: EndDate (Date/Time)

Table: Holidays
Field1: HolidayDate

Query: SELECT Employees.EmployeeID, Employees.[Start Date], Employees.[End Date], Employees.FullName, WorkingDays2([Employees]![Start Date],[Employees]![End Date]) AS SickDays
FROM Employees;


Function: WorkingDays2

Public Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer
'....................................................................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' 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 **************

This is actually part of a bigger access database but, as it wasn't working in there I created this test database but nothing seems to be working.

 

[PHV]

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

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[HacH]

Thank you PHV and dhookom. I spent days trying to figure out the issue but didn't find anything on the net that indicated issues with the date format.

All working now.