Z
Zeunasc
All of my attempts at modifying this function have failed, so I am
hoping someone here can give me some direction.
Basically, you take today's date, and find out what the date would
have been 90 working days ago. Now, by working days, I don't mean
standard working days (M-F). We work some Saturdays, so what I did
was create a table called tNonWorkingDays that has one column called
NonWorkingDays. The data in the table is just a list of dates that we
didn't work (every Sunday, every holiday, and the Saturdays that we
didn't work)
So, I basically need to count back 90 days from today, plus 1 day for
every non-working day contained in the table. Then, I can query my
data based on the date returned through today's date.
The function that I found lets me pass a starting date and an ending
date, and it returns the number of working days. I think it is
counting Saturdays and Sundays as non working days by default though.
It should be a simple mod to get what I need, but I can't figure it
out.
Any help is appreciated. If you need more info, please let me know.
TIA,
Tim
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 [NonWorkingDate] FROM
tNonWorkingDates", dbOpenSnapshot)
'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above
intCount = 0
Do While StartDate <= EndDate
rst.FindFirst "[NonWorkingDate] = #" & 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
hoping someone here can give me some direction.
Basically, you take today's date, and find out what the date would
have been 90 working days ago. Now, by working days, I don't mean
standard working days (M-F). We work some Saturdays, so what I did
was create a table called tNonWorkingDays that has one column called
NonWorkingDays. The data in the table is just a list of dates that we
didn't work (every Sunday, every holiday, and the Saturdays that we
didn't work)
So, I basically need to count back 90 days from today, plus 1 day for
every non-working day contained in the table. Then, I can query my
data based on the date returned through today's date.
The function that I found lets me pass a starting date and an ending
date, and it returns the number of working days. I think it is
counting Saturdays and Sundays as non working days by default though.
It should be a simple mod to get what I need, but I can't figure it
out.
Any help is appreciated. If you need more info, please let me know.
TIA,
Tim
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 [NonWorkingDate] FROM
tNonWorkingDates", dbOpenSnapshot)
'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above
intCount = 0
Do While StartDate <= EndDate
rst.FindFirst "[NonWorkingDate] = #" & 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