Solved

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

Posted on 2004-03-25
4
480 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
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…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

912 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now