$Account.OrganizationName
The Access Wizard Newsletter Tips Tricks and Traps for Access Users and Developers
August 2007

Date Problems - How Many Working Days between two Dates? - Part 4 - Generating SQL Statements

As I've said several times in the past, date math is complex (and I've spent several months talking about it by now). Our goal is to determine how many working days there are between any two given dates.

We already have a function to calculate the total number of weekdays between two dates (you can see that in the July 07 issue).

This month we'll write code to determine the total number of weekday holidays between two dates and then call on our total weekday function that we wrote last month to do the final calculation.

In this Issue
  • Tip of the Month - How to always go to code when using the Builder
  • How to Calculate Weekday Holidays
  • A Holiday Weekday SQL statement
  • Turning the SQL Statement into a Function
  • Testing the Function
  • Next Steps
  • Trap of the Month - Hiding Files with the Tools Menu is not Secure

  • How to Calculate Weekday Holidays

    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.


    A Holiday Weekday SQL statement

    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.


    Turning the SQL Statement into a Function

    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.


    Testing the Function

    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.


    Next Steps

    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?


    Trap of the Month - Hiding Files with the Tools Menu is not Secure
    Acess Traps

    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.


    Tip of the Month - How to always go to code when using the Builder

    When you're working in the design window of a form or report, frequently you'll want to create some code associated with a control. Typically you'd choose the control properties, click on the events tab then hit the ellipsis (. . . ) to the right of the event you are interested in.

    When you do this, you're presented with a dialog box titled "Choose Builder" with the options to use the Expression builder, Macro Builder or Code Builder.

    If you're like me, you'll almost always want to go directly to the code builder which will pop you into the code window where you can construct the logic for your event.

    If you'd like to, you can go to the code window automatically and skip the builder dialog box. To do so follow these steps:

    1. From the menu at the top of the screen, choose Tools, then selection Options. You'll be brought to a dialog box were you can set a number of defaults.
    2. Choose the Forms/Report tab.
    3. From there check the box that says "Always Use Event Procedures.

    Once you've done this, you'll skip the Builder dialog and pop immediately into the code window when you click on the event ellipsis.

    Quick Links...

    Custom Software Home

    Access Wizard Archives

    Our Services



    Join our mailing list!

    Forward email

    This email was sent to jim@custom-software.biz, by jim@custom-software.biz
     

    Custom Software | Copyright Custom Software All Rights Reserved | Westford | MA | 01886