How can I calculate working days between dates?

[Barb]

I am trying to calculate the number of days between a StartDate and EndDate
in Access while ignoring the weekends and holidays. I copied code and
directions from a posting in early November from Greg & Steve.

My main table is called Recruitment, and I have fields within that table
called StartDate, EndDate and Days. I also have a table called tblHolidays to
account for the holiday dates to be excluded.

I have put the following code into an Access module, which is in the main
form:

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 **************

My Control Source for Days is mdlWorkingDays 2([StartDate],[EndDate]). I
keep getting a #Name? error in my Days field.

Any idea of what may be wrong?

Thanks,

 

[Douglas J. Steele]

The sample code from Arvin you've posted has the function named
WorkingDays2, yet you say that you're using mdlWorkingDays
2([StartDate],[EndDate]) as the control source. They need to be the same.

And when you're saving the module, make sure that the name of the module is
not the same as the function. Name the module mdlWorkingDays2, but refer to
the function as WorkingDays2