Solved

date count exclusion: weekends and a holidays table with *date ranges*

Posted on 2004-03-25
4
483 Views
Last Modified: 2008-02-01
hello everyone!

i'm trying to do something rather complicated and wanted to see if anyone had any good ideas about how to implement this...  basically, i want to keep a table of holidays, and when i count the number of days between two date ranges, i want to be able to exclude the weekends and holidays.

here's the catch: the holidays table contains two types of holidays.  one is just the regular [HolidayDate] as a date format, but the other is a [HolidayDateRange] (data type text) with an input mask of xx/xx/xx - xx/xx/xx.

so.  i know there are slick procedures out there that can do an sql query on a holidays table to pull up the holiday count exclusions and then use the standard microsoft DateDiffW function to exclude the saturdays and sundays.

but here's how i'm going about this problem...

i created a form based on my holidays table, and then i cycle through the records with the me.recordset.movenext to take account of all the holidays.  when i hit a regular (single date) holiday, i don't have any problem.  (i've confirmed that it works properly.)  the difficulty comes in when i am trying to handle the holiday date range.  you can see that my code below is rudimentary at best, because the check against [thebegindate] and the range is going to have a problem with data such as:

[thebegindate] = 03/01/04
[theenddate] = 05/31/04

if there is a [HolidayDateRange] = 03/01/04 - 05/15/04

(in short, i need to subtract out the datediffw between the two dates in the holidaydaterange.  but if [theenddate] had been equal to 05/15/04, then i would have needed to subtract out all of the dates and return zero.  does any of this make sense...?)

thanx so very much for checking this out!
danielle brown
www.brownflower.com/danielletx

oh.  here's my code behind an on_click event of the holidays form:

---
    Dim calculatethediff, calculatethediffrange As Integer
    Dim rangebegin, rangeend As Date
   
    ' move to the first holiday
    Me.Recordset.MoveFirst
   
    ' formulate a rough count that doesn't include weekends
    calculatethediff = DateDiffW(thebegindate, theenddate)

    ' cycle through the holidays form to see if user input falls within any holidays
    While Not Me.Recordset.EOF
   
        'check the single date holidays
        If Not IsNull(holidaydate) Then
            If thebegindate <= holidaydate And theenddate >= holidaydate Then
                calculatethediff = calculatethediff - 1
            End If
           
        ' check the holiday date ranges
        ElseIf Not IsNull(holidaydaterange) Then
       
            ' take the holiday date range apart into a RangeBegin and a RangeEnd
            rangebegin = Trim(Left$([holidaydaterange], Len([holidaydaterange]) - InStr(StrReverse([holidaydaterange]), "-")))
            rangeend = Trim(Right$([holidaydaterange], Len([holidaydaterange]) - InStr(StrReverse([holidaydaterange]), "-")))
           
            If thebegindate <= rangebegin And theenddate >= rangeend Then
                calculatethediffrange = DateDiffW("d", rangebegin, rangeend)
                calculatethediff = calculatethediff - calculatethediffrange
            End If
        End If
        Me.Recordset.MoveNext
    Wend
           
    Me.Recordset.MoveFirst

    ' put the result of the count into the results box
    resultofdiff = calculatethediff
0
Comment
Question by:danielletx
  • 2
4 Comments
 
LVL 50

Expert Comment

by:Steve Bink
ID: 10679887
How about running a query on the holiday table to get all holidays within your date range?  Count 'em up, subtract from your difference, and there ya go!

SELECT Count(*) AS NumHolidays FROM Holidays WHERE [HolidayDate] BETWEEN #3/1/04# AND #5/31/04#

There's a couple of qualifiers, tho...

1) each day of each holiday must have a separate entry.  If you have holiday ranges for, say, Lent or Hanukah <SP?>, each day should be listed individually.  Pain in the butt, but it would work.
2) this does not account for holidays that will naturally fall on a weekend.  If you also remove the weekend days in your calculation, you will be removing double days for any holidays falling on Sat/Sun.
0
 

Author Comment

by:danielletx
ID: 10680989
good ideas here, but it's not really what i'm after (primarily because of the caveats you mention--especially number two).  hmmm.  i think i can handle this through vb code as long as i can manage to have someone help me think through the ends and outs of going through the range and doing the right comparisons.  one of the main advantages of this will be that i'll avoid your caveat number two, because i can use datediffw to exclude the weekends from the holiday range.

thanx for your suggestions, though!
0
 
LVL 50

Accepted Solution

by:
Steve Bink earned 250 total points
ID: 10681135
You could try a variation of that idea.  A little more complex, though...

First: rs = CurrentDb.OpenRecordset "SELECT * FROM Holidays WHERE [HolidayDate] BETWEEN #3/1/04# AND #5/31/04#"

Gives you the holidays you're looking for.  Pass the recordset to a new sub designed to find the holidays and weekends.  This function just returns the number of days found in the recordset that are NOT on a weekend.  When you get the result back, you can subtract this number from the number of days in the range, subtract the weekend days (using your DateDiffW method), and wah-lah!  Caveat #2 is no more.  To get around #1, you could specify a field in the holiday table for number of days for that holiday, say [HolidayNumDays], and use that number to count instead of the x=x+1.

Public Function I_Hate_Holidays_And_Weekends(rs as DAO.Recordset) as Integer
Dim x

x=0
do while not rs.eof
    Select Case of Weekday(rs![HolidayDate])
        Case 2-6: x=x+1
        Case Else: ' ...uuuhhhh...don't really need it I guess.
    End Select
    rs.MoveNext
loop
I_Hate_Holidays_And_Weekends = x

End Function
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question