Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2004-03-25
4
Medium Priority
?
507 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 51

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 51

Accepted Solution

by:
Steve Bink earned 750 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

610 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