Remember that our goal is to generate a count
of holidays that are also workdays. We do this
with a SQL statement and the easiest way to
generate a SQL statement is by creating a
query, going to the SQL view, and copying the
contents of the window.
Our query looks like this:
The relevant columns are:
Holiday Date >=#1/1/2007# And
<=#12/31/2007#
Basically, in this example we're creating a
query for the 2007 calendar year. Translating
this into English, we have the HolidayDate is
greater than or equal to Jan 1, 2007 and less
than or equal to December 31, 2007. The
actual dates we use in our query are irrelevant
at this stage. We're simply using them as
placeholders, which we'll swap out for our time
period of interest when we start manipulating
the SQL statement.
Our second column has the criteria
Workday = false
Here we eliminate holidays that we've checked
as workdays (days not considered holidays for
the business; see the May
2007 issue for including these working
holidays in the table in the first place).
Our third column is:
WkDay: Format([HolidayDate],"ddd")
This statement replaces the date in the
HolidayDate field shown in the Holiday table
with the 3 character code for the day of the
week (e.g., Mon, Tue, Wed).
The criteria field: <>"Sat" And <>"Sun"
eliminates holidays from the resulting set that
fall on a Saturday or Sunday during the
defined time period.
There are several more efficient ways to
accomplish this rather than using a formatting
function, but I chose this method because it's
much easier to debug (if necessary).
When we run this, we get:
At this point this is little more than a pretty
picture that shows the results of our query.
What's important to notice is that none of the
returned records include a Saturday or a
Sunday (we don't want to double count those,
and none of the holidays marked as a workday
(e.g. Halloween) is included.
Once you've done this and are satisfied that
no working holidays are included and there
are no Saturdays or Sundays, go back to
design view and remove the checks from all of
the columns except HolidayDate. Although this
is not strictly necessary it will declutter our
SQL statement and make things run a tad
faster,
To get to the SQL statement, which is
what we really want, click on the view menu
item at the top of the screen and you'll see:
SELECT tblHoliday.HolidayDate
FROM tblHoliday
WHERE (((tblHoliday.HolidayDate)
>=#1/1/2007# And
(tblHoliday.HolidayDate)<=#12/31/2007#)
AND
((tblHoliday.Workday)=False) AND
((Format([HolidayDate],"ddd"))<>"Sat" And
(Format([HolidayDate],"ddd"))<>"Sun"));
This is the code to query the database and is
what's really happening behind the scenes.
This is also what we'll use and manipulate in
our code.
At this point we have a SQL statement that
we'll use to determine the number of working
holidays. There are two tasks that we have to
accomplish to make this happen. The first is to
turn the SQL Statement into something that
can be used in a function. The second is to
make the SQL statement generic so that it can
handle whatever dates are necessary.