Calculate Workdays Only in query NO VB Please!

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

Is there a way within a query to take a begining date and
an ending date and count only the workdays. I've seen
examples done in VB but I would like to know if it can be
done in a query.
 
There is no in-built Access or VBA functions to eliminate the weekend (and
public holidays if you want to be precise!). You need a UDF to di this,
i.e. VBA code.
 
Dear Bill:

I haven't done this myself, but it should be possible.

I'm going to suggest a table containing all the dates for several
years - at least the period over which you want to be able to figure
workdays.

You can COUNT() the rows from this table BETWEEN the dates you want,
and you can add criteria to eliminate any day(s) of the week. In
addition, there should be a column in this new table where you can
specify holidays. I'm thinking this column could be NULL for
non-holidays, and contain the name of code number of any holidays you
want to declare.

Your query (probably a subquery) that counts workdays between dates
could then do something like:

(SELECT COUNT(*) FROM Workdays
WHERE TheDate BETWEEN StartDate AND EndDate
AND Weekday(TheDate) IN (2, 3, 4, 5, 6)
AND (Holiday IS NULL
OR Holiday NOT IN ("New Years Day", "Lincoln's Birthday", "July
4", "Christmas")))

The last part:
OR Holiday NOT IN ("News Years Day", . . .
is meant to allow you to customize the query. Once you have entered
upwards of 200 holidays, you can choose which you want to respect and
which you do not. You can also create yet another table that sets up
and names groups of holidays, what I'll call a holiday group. Thus,
there would be the "Standard US Holiday" group, a "Jewish Holiday
Group", and many other combinations. I'm just designing in enough
"power" so that the system can be easily leveraged to do many
additional things, not that you are going to have to set them up or
even create forms to maintain this initially.

A separate table of holidays would be desirable.

There's a possible start for you.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top