Nominated Date+Nominated number of workdays

[Guest]

Hi,
I am looking for an expression that I can put into a form.
The database is a billing database that has a date when the incovice is
rendered and to determine the due date I need to add a nominated number of
business days.
(ie:17/07/06 + 20 Business days)

Has any one got a suggestion as this is a bit different to the datediff
function.

Thanks in advance.

Chris

 

[Douglas J. Steele]

Take a look in the Date/Time section of "The Access Web"
http://www.mvps.org/access/datetime/index.html, or check out my September,
2004 "Access Answers" column in Pinnacle Publication's "Smart Access".

You can download the column (and sample database) for free at
http://www.accessmvp.com/djsteele/SmartAccess.html

 

[Ron2006]

Chris,
It was not elegant and there may be some easier ways but I solved the
problem by creating a subroutine that

received fromdate, howmanyworkdays, endingdate(computed this one)

added 1 to fromdate
if it was holiday or sunday add 1 to from date but did not subtract
from work day - start loop again.
if it was saturday add 2 to from date but not subtract from work day.-
start loop again.
subtract 1 from howmany work days
if howmany workdays > 0 start loop again.

Ron

 

[Guest]

Thanks worked well.

 

[qsl_nz]

Take a look in the Date/Time section of "The Access Web"
http://www.mvps.org/access/datetime/index.html, or check out my
September,
2004 "Access Answers" column in Pinnacle Publication's "Smart
Access".

Douglas, I've used this download (excellent thanks) which gives me
the answer I want, but because of the time frames involved it does
take a while.

I have a start date, given number of calendar months to add to the
start date, so therefore have the end date - but I don't want this to
be a Saturday/Sunday or a public holiday. I have a table based on
location of public holidays (as in your article).

I have 46 rows to which I update the "Due Date" based on the number
of days called "Freq" custom set for different criteria, ([StartDate]
+ [Freq]; some of these values in "Freq" are 1 year; thereby taking
some time to process the Due Dates.

Can you guide me on modifying your module (AA200409 -
HolidayWorkDayAdd)such that the Start Date plus Given Days gives
Possible Date whereby this code then runs to give the Due Date, as
not a Saturday,Sunday or Public Holiday after the Possible Date(my
thoughts are this will mean the code only needs to loopthrough a few
days for each row, rather than up up to a year...).

Many thanks
Matt