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!
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
' 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
' 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$([holidaydateran
ge], Len([holidaydaterange]) - InStr(StrReverse([holidayd
rangeend = Trim(Right$([holidaydatera
nge], Len([holidaydaterange]) - InStr(StrReverse([holidayd
If thebegindate <= rangebegin And theenddate >= rangeend Then
calculatethediffrange = DateDiffW("d", rangebegin, rangeend)
calculatethediff = calculatethediff - calculatethediffrange
' put the result of the count into the results box
resultofdiff = calculatethediff