Back in the May 2007 issue I made the case for using the Holiday calendar below, which will allow the user to define a holiday as workday by simply checking a box in the "workday" column:

In order to calculate the total number of workdays between two dates we have to eliminate any holidays that occur on work days. Some holidays, like Mother's Day, always fall on a weekend. But some holidays, like New Year's Day and Christmas, move around from year to year. If the holiday happens to be a Saturday or a Sunday, it shouldn't be subtracted from our set of total workdays, so when we subtract holidays, it's important to not subtract holidays that occur on weekends.

There are several ways to approach this.

I think the easiest way is to write a function that uses a record set. A record set is a set of records from your database that you can act upon or view. By act upon I mean: edit, add to, delete, count or inspect in some way. For our purposes all we really need to do is count the number of holidays between two dates that occur on a weekday.

We'll start with a SQL statement. What's a SQL statement? If you think of it as the guts behind a query you'll come pretty close; for a more complete explanation see the June 2005 Wizard.

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.

To turn the SQL statement into code: open a new code window, create a function called Working Holidays, and type in (or copy) the following code:

Function HolidaysOffCount(dtmBegin As Date, dtmEnd As Date) As Long
   Dim db As DAO.Database
   Dim rst As DAO.Recordset
   Dim strSQL As String
   Dim rv As Long
   Set db = CurrentDb
   strSQL = tbd
   Set rst = db.OpenRecordset (strSQL)
   If rst.EOF And rst.BOF Then
     rv = 0
     Else
     rst.MoveLast
     rv = rst.RecordCount
   End If
   HolidaysOffCount = rv
End Function

Then switch back to the query in SQL view and copy the contents of the SQL statement onto your clipboard. Then highlight "tbd" on the strSQL = tbd line and then paste the clipboard contents back into your code window.

You'll immediately see that Access becomes unhappy and starts throwing error statements and turning your code red indicating a problem. The reason it does this is because it sees that what you've pasted in is not valid code. So our next step is turn the SQL statement into something that Access will understand when it shows up in code.

The way to do this is to turn the SQL statement into a valid string. You note that the line that declares the strSQL variable is a string variable and in order to create a valid string we have to eliminate all line breaks and enclose the whole SQL statement within quotes.

There are three steps to this. The first is to replace every instance of a double quote with a single quote. The reason for this is that we'll be using double quotes to begin and end our strings. If we leave in existing double quotes, things will get confusing very quickly. If we replace the double quotes with single quotes, Access will recognize the single quote as part of the larger string and interpret it correctly.

The next step is to put quotation marks at the very beginning and very end of every line starting with the word Select and ending with the final semi-colon.

The third step is to make one continuous string by telling Access to ignore line breaks. We do this by using the characters "& _" (without the quotes) at the end of each line except for last line. This tells Access that the next line is simply a continuation of the current line. The trick though is that you must also end each line, prior to the ampersand, with a double quote. It's also a good practice to include a space just prior to the double quote to ensure that intended spaces are included. When you do this your SQL statement will look like this

strSQL = "SELECT tblHoliday.HolidayDate, tblHoliday.Workday, " & _
   "Format([HolidayDate],'ddd') " & _
   "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'));"

Now there is no question that turning a SQL statement into valid code, as we have here, is a persnickety process. I have a tool that I use to help me in this whenever i have to do this task.

Our Function at this point looks like this:

Function HolidaysOffCount(dtmBegin As Date, dtmEnd As Date) As Long
   Dim db As DAO.Database
   Dim rst As DAO.Recordset
   Dim strSQL As String
   Dim rv As Long
   Set db = CurrentDb
   strSQL = "SELECT tblHoliday.HolidayDate, tblHoliday.Workday, " & _
   "Format([HolidayDate],'ddd') " & _
   "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'));"
      Set rst = db.OpenRecordset (strSQL)
   If rst.EOF And rst.BOF Then
     rv = 0
     Else
     rst.MoveLast
     rv = rst.RecordCount
   End If
   HolidaysOffCount = rv
End Function

To test it in the immediate window, press (Control-G to get there) and then type:

? HolidaysOffCount(#1/1/2007#, #1/1/2007#)

If you've used the same table I have you'll get a count of 11 holidays which are not workdays and therefore must be subtracted from our time period to get the calculation of total workdays, which matches what we saw when we ran our query originally.

We now have a function that will calculate Holidays that are NOT workdays (they constitute days off). The function has a major flaw, however, in that the dates are "hard- coded." Next month we'll do three things:

1. We' adjust to code so that it uses whatever dates are passed in.
2. I'll step you through the parts of the code that are not immediately clear
3. We'll use our newly created function as the last piece of our puzzle and we'll finally have the answer to our question: How many working days are there between two dates?

It's very easy to hide files from users . . .even files you didn't intend to hide.

This month's Tip shows you how to hide tables from view. The option to show or hide files is not difficult to get to from the Tools menu, and once executed it is in effect across the board. So be aware of this option, but use it carefully to hide things only from casual users. Any knowledgeable Access user, if curious, can see the files you've hidden just by using menu options.

There are many more powerful techniques to hide objects, and I'll explain some of them in future issues of the Access Wizard.