Z
Zeunasc
I found a query while searching the newsgroups that I think will
accomplish exactly what I want. I will include the function after I
finish this post. Anyway, the name of the function is WorkingDays2
and it's purpose is to count the number of working days from one date
to another, taking in to consideration holidays, Saturdays, Sundays,
etc. I have the issue where we work some Saturdays, but not all, so
it is very hard to do.
Anyway, I haven't ever used a function in Access before, so I am not
sure what to do with it. I know that I have to define it as a PUBLIC
function, and it goes in the Modules page. But, once I have it there,
how do I use it in a query?
If you need more info, please let me know.
TIA,
Tim
Here is the Function:
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
accomplish exactly what I want. I will include the function after I
finish this post. Anyway, the name of the function is WorkingDays2
and it's purpose is to count the number of working days from one date
to another, taking in to consideration holidays, Saturdays, Sundays,
etc. I have the issue where we work some Saturdays, but not all, so
it is very hard to do.
Anyway, I haven't ever used a function in Access before, so I am not
sure what to do with it. I know that I have to define it as a PUBLIC
function, and it goes in the Modules page. But, once I have it there,
how do I use it in a query?
If you need more info, please let me know.
TIA,
Tim
Here is the Function:
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