Solved

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

Posted on 2004-03-25
4
487 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

733 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