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

Date Problems - How Many Working Days Between Two Dates: Part 2 - Eliminating Weekends

Eliminating weekends? What a horrible idea! I love weekends. It's a fun time for family, tennis and geocaching. However, since our task at hand is calculating the number of workdays between two dates, one part of our puzzle is eliminating weekends. This month we'll write a function to determine if a date falls during a weekend, thus not counting it as a workday.

Last month we began the journey of calculating the number of work days between two dates. Our plan requires us to execute four steps:

  1. Determine what constitutes a holiday
  2. Calculate the number of weekdays between two dates
  3. Calculate the number of holidays between two dates
  4. Perform the math (calculate total weekdays minus defined holidays)

In May we addressed item one. This month, we'll lay the foundation to tackle the second step - how many working days are there between two dates?

In this Issue
  • Tip of the Month - Hide a Table From Users
  • Review: Defining Holidays
  • Calculating weekdays between two dates.
  • Determining if any given date falls on a weekend.
  • Conclusion and Next Steps
  • Trap of the Month - Hiding Files with the Tools Menu is not Secure

  • Review: Defining Holidays

    Last month I made the case that a holiday is best determined by the use of a holiday table which you must get or generate - there are no built in holiday tables in Access.

    For our purposes we'll use the one below. As you can see, there is a handy checkbox column that allows an administrator to review the list and check if the holiday is a workday.


    Calculating weekdays between two dates.

    Your initial approach to calculating the number of weekdays between two dates may involve using the DateDiff function to calculate the total number of days between two dates and then eliminating weekends. Eliminating weekends, however, would require looking at the calendar to evaluate whether your date span starts or ends on a weekend and counting weekends to subtract in between the endpoints -- way too much trouble and prone to errors. Instead, we'll take the easy way out.

    If we can knock out any weekend days at the beginning and end of our date span (and we can, using a function I'll present shortly), then our task becomes first calculating the number of full weeks between the two dates and then subtracting the number of weekend days. To calculate the weekend days during our period all we have to do is figure out the number of full weeks during the period and multiply by two (because there are two weekend days (Saturday and Sunday) for every full week).


    Determining if any given date falls on a weekend.

    Now we'll walk through a function step-by-step that will allow us to determine if any given date (like the dates on either end of our date span) are weekend days. As there is no predefined weekend function in Access, we have to create our own:

    Function IsWeekend(dtm As Date) As Boolean
       Dim rv As Boolean
       Select Case Weekday(dtm)
         Case Is = vbSaturday, vbSunday
           rv = True
         Case Else
           rv = False
       End Select
       IsWeekend = rv
    End Function

    The input to this function IsWeekend is a date; the output is True or False; true if the date is Saturday or Sunday, false otherwise.

    Let's go though the code line by line.

    Dim rv As Boolean

    This line creates a variable rv (which stands for Return Variable) to make our lives easier. I could omit this variable and replace every "rv" in the code with "IsWeekend", however I much prefer to type two letters rather than seven when I'm writing code.

    Our next section of code is bracketed at the beginning with Select Case and at the end with End Select. The Select Case construct is similar to a set of IF/THEN statements except that the code between each line that starts with the word Case is evaluated in a stepwise manner. If the statement is true, it will execute all the code between that Case line and the next Case line. If it's not true, it will skip to the next Case line. Once one of the lines starting with Case is satisfied (rv is deemed to be either true or false), the function jumps to End Select.

    The line

    Select Case Weekday(dtm)

    uses a built-in Access function called Weekday to evaluate our argument dtm (date). This function takes one variable, a date, and returns a number from 1 to 7 representing the day of the week. There's a bit of complexity behind the scenes in determining the first day of the week. We avoid that by using the Access internal constants vbSaturday and vbSunday .

    From there the code is straightforward and mainly a matter of syntax:

    Case is = vbSaturday, vbSunday

    translates to "if the case of the incoming date is a Saturday or a Sunday then go to the next line, otherwise jump to the next Case Statement."

    If it is a Saturday or a Sunday the line

    rv = true

    is executed, which means that our return variable is now set to true. If the incoming date is not a Saturday or a Sunday the next line to be executed is

    Case Else

    The case else line within a Select Case construct basically says that if none of the case statements evaluates to true then do the Select Case statements. Theoretically we could include lines like

    Case is = vbMonday
       Rv = false
    Case is = vbTuesday
       Rv = false

    However if it's not a Saturday or a Sunday, then we know the date is not a weekend so we can just jump to our Case else statement. The line following Case Else

    rv = False

    simply sets our return variable to false.

    The next line

    IsWeekend = rv

    sets our function return value equal to our rv variable, and then we complete the function with our End Function


    Conclusion and Next Steps

    Now we have a function we can call to determine if a given date is a Saturday or Sunday. If it is, we'll eliminate it from the beginning or end of our date span and then set forth to figure out how many complete weeks remain in the selected period. Next month we'll write the code that will use this function to calculate the total number of weekdays between two given dates. After that we'll subtract the non-working holidays between the dates 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 - Hide a Table From Users

    If you have a table in your Access application that you don't want casual users to see or open, there is a very simple method to hide it.

    In the database view, right click on the table you'd like to hide and choose Properties.

    One of the properties available in this window is Hidden. To hide the table, simply click in the Hidden checkbox and the table will disappear from the standard Database view.

    To make the table reappear, click on Tools on the menu at the top of your screen. That will bring up a dialog box with a series of tabs. If the View tab is not highlighted by default (it usually is) click on it and then in the area marked Show, click on the box that shows hidden objects.

    At this point you can return to your table to make it visible again by unchecking the Hidden checkbox. After you've done that, you may want to turn off the hidden objects option again so that other hidden objects stay hidden.

    In addition to hiding tables, you may also hide queries, forms and reports, macros and modules.

    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