Solved

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

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

743 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

12 Experts available now in Live!

Get 1:1 Help Now