Access Number of Days Query

[Morena]

Is there an access query way of counting the number of
working days per month.
I tried to use the function networkdays and workdays but
access did not recognise these function.
Thank in anticipation

 

[John Vinson]

Is there an access query way of counting the number of
working days per month.

It's not altogether trivial, since different countries and different
companies recognize different holidays! It's not just weekends that
need to be considered; you'll also need a table of the holidays
recognized by your company.

I tried to use the function networkdays and workdays but
access did not recognise these function.

Blame Microsoft for this one. These are Excel functions, not Access
functions, and are not available in Access, despite their presence in
the Help index. GRRR!!!

See http://www.mvps.org/access/datetime/date0012.htm for some sample
code to achieve this capability in Access.

 

[Tim Ferguson]

Is there an access query way of counting the number of
working days per month.

PARAMETERS InYear NUMBER, InMonth NUMBER;
SELECT DAY(DATESERIAL([InYear], [InMonth], 0) -
( SELECT COUNT(*) FROM Holidays
WHERE Holidays.HolDate >= DATESERIAL(InYear, InMonth,1)
AND Holidays.HolDate < DATESERIAL(InYear, InMonth+1,1)
)


The first line -- Day(DateSerial) gives the number of days in a month, and
the subselect counts the number of holidays in a table of holidays. I've
just realised that this doesn't do the weekends, but it shouldn't be too
hard to do a bit of WEEKDAY and MOD 7 stuff to get them out too.


HTH


Tim F