Help modifying a "working days" query/function

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

Assuming you already have a function that efficiently compute the number of
working days between two dates, the following algorithm can be use:

guess1= today + 90 ' guess that 90 working day, from now, will be today+90
error1=90-MyFunction( today, guess1) ' ie, we are short of how many days,
with that guess

guess2=guess1+error1
error2=90-MyFunction(today, guess2)

etc., until the error =0


where MyFunction is the function returning the number of working days
between the two given dates. Sure, if that function just 'count one by one'
the days, the proposed algorithm is quite inefficient. Then, try:

wanted =90
actual = today

A0:
actual = actual + 1
if( actual is a working day) then
wanted = wanted-1
end if

if( wanted =0 ) then
return the value of actual
end if
go to A0



which also counts the days one by one, but a given day will be examined just
once, in this case.



Hoping it may help,
Vanderghast, Access MVP

"Zeunasc" wrote in message
oups.com...
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

In addition to Michel's esteemed advice, you can probably
adapt following (may be off by one day?):

'**untested**
Public Function fSubtractBusinessDay(pStart As Date, pSub As Integer)
'Subtracts the proper Business days
'skipping days in tNonWorkingDates (NonWorkingDate)
'adapted from code by Arvin Meyer 05/26/98
On Error GoTo Err_fSubBusinessDay

Do While pSub 0
pStart = pStart - 1
If DCount("*", "tNonWorkingDates", "[NonWorkingDate]=#" & pStart & "#")
= 0 Then
'not an "Off Day"
pSub = pSub - 1
End If
Loop

fSubtractBusinessDay = pStart

Exit_fSubBusinessDay:
Exit Function

Err_fSubBusinessDay:
MsgBox Err.Description
Resume Exit_fSubBusinessDay
End Function


"Zeunasc" wrote:
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

I actually got this working in the meantime... Now, I have a function
that will return a date that is 90 days in the past, taking in to
consideration anything contained in the tNonWorkingDates table.

However, I don't know how to implement this into a query. That is,
the query returns the End date, and the Start Date should be today.
So, how do I create a query return results limited to the time between
these dates (one from a builtin function like Now(), and one from the
custom function)?

Below is the function that is working.

TIA,
Tim

Public Function basMtgDate() As Date

Dim dbs As DAO.Database
Dim rstHolidays As DAO.Recordset
Dim MyDate As Date
Dim MyDays As Long
Dim strCriteria As String
Dim NumSgn As String * 1

Set dbs = CurrentDb
Set rstHolidays = dbs.OpenRecordset("tNonWorkingDates",
dbOpenDynaset)
startdate = Now()
NumDays = 30
NumSgn = Chr(35)

If (IsMissing(startdate)) Then
startdate = Date
End If

MyDate = Format(startdate, "Short Date")

Do While MyDays NumDays
strCriteria = "[NonWorkingDate] = " & NumSgn & MyDate
& NumSgn
rstHolidays.FindFirst strCriteria
If (rstHolidays.NoMatch) Then
MyDays = MyDays + 1
Else
'Do Nothing, it is NOT a Workday
End If

If (MyDays = NumDays) Then
Exit Do
End If

MyDate = DateAdd("d", -1, MyDate)

Loop

basMtgDate = MyDate

End Function

To use in a query on a date field (say "ProjDate")
whose time portion is not always "00:00:00"

to return records for project in last 90 business days...

WHERE
[ProjDate] = fSubtractBusinessDay(Date(), 90)
AND
[ProjDate] Date() +1

"Gary Walter" wrote:
In addition to Michel's esteemed advice, you can probably
adapt following (may be off by one day?):

'**untested**
Public Function fSubtractBusinessDay(pStart As Date, pSub As Integer)
'Subtracts the proper Business days
'skipping days in tNonWorkingDates (NonWorkingDate)
'adapted from code by Arvin Meyer 05/26/98
On Error GoTo Err_fSubBusinessDay

Do While pSub 0
pStart = pStart - 1
If DCount("*", "tNonWorkingDates", "[NonWorkingDate]=#" & pStart & "#")
= 0 Then
'not an "Off Day"
pSub = pSub - 1
End If
Loop

fSubtractBusinessDay = pStart

Exit_fSubBusinessDay:
Exit Function

Err_fSubBusinessDay:
MsgBox Err.Description
Resume Exit_fSubBusinessDay
End Function


"Zeunasc" wrote:
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