Work Day?

[Guest]

I have a report using a text box which uses the control source as =Date() + 1.
This works fine for Tues thru Fri. But when I need to print Monday's report
I don't want to have to go in and change the + 1 to + 3. And if there is a
holiday on Monday, I would like the expression to know this.

I do have a table listing all our holidays, but am stuck on the code to use
so report knows when it is a holiday or weekend.

Any help is appreciated. Great help is even better.

 

[Dirk Goldgar]

I have a report using a text box which uses the control source as
=Date() + 1. This works fine for Tues thru Fri. But when I need to
print Monday's report I don't want to have to go in and change the +
1 to + 3. And if there is a holiday on Monday, I would like the
expression to know this.

I do have a table listing all our holidays, but am stuck on the code
to use so report knows when it is a holiday or weekend.

Any help is appreciated. Great help is even better.

The link below has various functions to do workday math, including one
to add a specified number of work days. Note that some functions rely
on other functions, so you probably need to include all of them in your
module.

http://www.mvps.org/access/datetime/date0012.htm
Date/Time: Doing WorkDay Math in VBA

 

[Jamie Collins]

Date() + 1.
This works fine for Tues thru Fri. But when I need to print Monday's report
I don't want to have to go in and change the + 1 to + 3. And if there is a
holiday on Monday, I would like the expression to know this.

I do have a table listing all our holidays

What does your Calendar table look like? e.g.

SELECT MIN(C1.date_col)
FROM Calendar AS C1
WHERE C1.date_col > arg_date
AND C1.is_workday = 1;

Jamie.

--