Networkdays Formula in Access

#11

"Vikas Chauhan" <vikask008 (AT) gmail (DOT) com> wrote

Quote:

how to use??

It depends on what you are doing.

You can use it as the control source of a for or report, in a function or
even in a query. Note: dcount will slow your query to a crawl if you don't
restrict the record count.

Quote:

On Jun 8, 6:04 pm, "ron paii" <n... (AT) nospam (DOT) com> wrote:
"Vikas Chauhan" <vikask... (AT) gmail (DOT) com> wrote in message

news:8ea1b206-b5f2-4a85-8597-b5f0996bc8ac (AT) y27g2000prb (DOT) googlegroups.com...

Hi Gurus,

Thnx for reply !!!

But my problem still not shotout..

can u give me sample if is it possible.

regards,
vikas

On Jun 8, 3:31 am, "ron paii" <n... (AT) nospam (DOT) com> wrote:
"Bob Barrows" <reb01... (AT) NOyahooSPAM (DOT) com> wrote in message

news:islces$qo9$1 (AT) dont-email (DOT) me...

ron paii wrote:
"Bob Barrows" <reb01... (AT) NOSPAMyahoo (DOT) com> wrote in message
news:isl28t$q1h$1 (AT) dont-email (DOT) me...
There are two approaches to this problem. The first involves using
VBA as discussed in this article:
http://access.mvps.org/access/datetime/date0006.htm
Specifically look at the second method since it takes holidays
into
account. Read it carefully! Especially the part where it explaiins
that you need to create a table to store the dates you wish to
treat
as holidays!

snip, please!
Following is 1 line of VBA that will calculated the number of work
days, excluding holidays between 2 dates.

WorkDayCount = DateDiff("d", dtStartDate, dtEndDate) + 1 -
(DateDiff("ww", dtStartDate, dtEndDate) * 2) -
IIf(WeekDay(dtStartDate) = vbSunday, 1, 0) - IIf(WeekDay(dtEndDate)
=
vbSaturday, 1, 0)

That's certainly a viable alternative to the method shown in first
function
in the above article. I would change it to "WeekDayCount=" since
that
is a
better description of what it counts..

Subtract out holidays from this count, using a function to count
holidays in the range of dates.

That's the hard part. Why did you leave out how to do it? ;-)
Fortunately, the article cited above describes how to do this part
as
well.
It's just not as flexible as the second approach I described. If the
OP
does
not need that flexibility, then I certainly recommend he use a
combination
of your expression and tblHolidays as described in the above
article.

With tblHolidays, you could do a dcount on the range of dates. My
function
saves tblHolidays to a static array on the 1st call. This greatly
speeds
up
the holiday search on all other calls. I left it out because it had
added
complexity specific to my application.

Here is the workday count calculation with holidays. Change tblHolidays
to
your holiday table name and [dtDate] to the name of your date field.

WorkDayCount = DateDiff("d", dtStartDate, dtEndDate) + 1 -
(DateDiff("ww",
dtStartDate, dtEndDate) * 2) - IIf(WeekDay(dtStartDate) = vbSunday, 1,
0) -
IIf(WeekDay(dtEndDate) = vbSaturday, 1, 0) -
dcount("[dtDate]","tblHolidays","[dtDate] between #" & dtStartDate & "#
and
#" & dtEndDate & "#")

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Networkdays Formula in Access

comp.databases.ms-access comp.databases.ms-access

Re: Networkdays Formula in Access - 06-10-2011 , 07:06 AM