Access Number of Days Query

  • Thread starter Thread starter Morena
  • Start date Start date
M

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
 
Is there an access query way of counting the number of
working days per month.
Click to expand...

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.
Click to expand...

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.
 
Is there an access query way of counting the number of
working days per month.
Click to expand...

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
 
Back
Top