Solved

Removing weekend days and holidays from a date range when reporting metrics.

Posted on 2006-06-22
7
522 Views
Last Modified: 2008-01-16
I scanned the database and found some other questions along this line, but could not figure out how to make it work.  

I've been asked to report some metrics for the last 'x' business days, so basically I need to figure out a date to use for my report by subtracting ‘x’ from the current date and count back to a beginning date excluding all the weekends and holidays.

On a form (frm_Test) I have . . .

1. . . a text box that holds ‘x.’ (txtNumDays)
2. . . a text box to reflect the date I want to use for reporting:  txtRptBeginDate,  =DateValue(Now()-[txtNumDays])
3. . . a command button that opens a report and shows the data based on a query that uses the txtRptBeginDate.  (>=[Forms]![frm_Test]![txtRptBeginDate])

All works well except I need to not count the weekends and holidays when I go back to the txtRptBeginDate.  I can make a calendar table if necessary.  Can someone help me with the code to make this work?  Thank you!
0
Comment
Question by:c9k9h
[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
  • 2
  • 2
  • +1
7 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 16963747
Got this code from MS some time ago, given two dates, it calculates the number of working days


Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer

   ' Note that this function does not account for holidays.

   Dim WholeWeeks As Variant
   Dim DateCnt As Variant
   Dim EndDays As Integer
         
   On Error GoTo Err_Work_Days

   BegDate = DateValue(BegDate)
   EndDate = DateValue(EndDate)
   WholeWeeks = DateDiff("w", BegDate, EndDate)
   DateCnt = DateAdd("ww", WholeWeeks, BegDate)
   EndDays = 0
   Do While DateCnt <= EndDate
      If Format(DateCnt, "ddd") <> "Sun" And _
        Format(DateCnt, "ddd") <> "Sat" Then
         EndDays = EndDays + 1
      End If
      DateCnt = DateAdd("d", 1, DateCnt)
   Loop
   Work_Days = WholeWeeks * 5 + EndDays

    Exit Function

Err_Work_Days:

    ' If either BegDate or EndDate is Null, return a zero
    ' to indicate that no workdays passed between the two dates. '

    If Err.Number = 94 Then
                Work_Days = 0
    Exit Function
    Else
' If some other error occurs, provide a message.
    MsgBox "Error " & Err.Number & ": " & Err.Description
    End If

End Function
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 16963815
see this link

How to determine if a date falls on a weekend or  on holiday in Access

http://support.microsoft.com/?kbid=290152
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 16964771
c9k9h ...

In conjuction with the method posted in capricorn1's link you could do the following ....

For the OnClick event of your command button ...

Private Sub YourCommand_Click()

Dim varCounter As Long
Dim varDate As Date

varCounter = Me.txtNumDays
varDate = DateAdd("d", -1, DATE)

    Do Until varCounter = 0
        If DatePart("w", varDate) = 1 Or DatePart("w", varDate) = 7 Or DLookup("[Holidate]", "Holiday_Table", "[Holidate] = #" & varDate & "#") = varDate Then
            varDate = DateAdd("d", -1, varDate)
        Else
            varDate = DateAdd("d", -1, varDate)
            varCounter = varCounter - 1
        End If
   
    Loop
   
    Me.txtRptBeginDate = varDate + 1

'Then add to code to run your report here ....

End Sub

ET
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 19

Expert Comment

by:Eric Sherman
ID: 16964796
You also may want to default txtNumDays to 0 and add the following code at the beginning of the sub to prompt the user in case the field is null.


Private Sub YourCommand_Click()

If IsNull(Me.txtNumDays) Then
    MsgBox "Please enter a value for the Number of Days!!!!"
    Exit Sub
End If


Dim varCounter As Long
Dim varDate As Date

varCounter = Me.txtNumDays
varDate = DateAdd("d", -1, DATE)

    Do Until varCounter = 0
        If DatePart("w", varDate) = 1 Or DatePart("w", varDate) = 7 Or DLookup("[Holidate]", "Holiday_Table", "[Holidate] = #" & varDate & "#") = varDate Then
            varDate = DateAdd("d", -1, varDate)
        Else
            varDate = DateAdd("d", -1, varDate)
            varCounter = varCounter - 1
        End If
   
    Loop
   
    Me.txtRptBeginDate = varDate + 1

'Then add to code to run your report here ....

End Sub

ET
0
 

Author Comment

by:c9k9h
ID: 16969309
Thank you all.  Capricorn1 had the closest to what I was looking for and what I was able to understand to work with.  This is what I ended up with and it is working fine.

Private Sub cmdOpenReport_Click()
   
Dim intCount As Integer
Dim intDays As Integer

intCount = txtDays.Value
intDate = DateValue(Now())

Do Until intCount = 0

    'Test for Weekend day.
    If Weekday(intDate) = 1 Or Weekday(intDate) = 7 Then
    'Do not decrement intCount - in other words - do nothing.
       
        ' Test for Holiday.
        ElseIf Not IsNull(DLookup("HoliDate", "tbl_HolidayDates", "[HoliDate]=#" _
            & intDate & "#")) Then
            'Do not decrement intCount.
           
        Else:
       
        intCount = intCount - 1
        'Decrement the number of days entered to go backwards through the dates.
    End If
       
    intDate = intDate - 1
   
Loop

txtRptBeginDate.Value = intDate
txtRptBeginDate.Visible = True
     
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.OpenReport "rpt_ActionTaken", acViewPreview
 
End Sub
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16969365
Remember to change the holiday dates then
Only once a year though
0
 

Author Comment

by:c9k9h
ID: 16970838
Yes, you're right rockiroads!  I'm hoping to connect to the master holiday table in the company that somebody else keeps updated.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

733 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