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

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