Solved

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

Posted on 2006-06-22
7
518 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
  • 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 119

Accepted Solution

by:
Rey Obrero 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

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…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

919 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

20 Experts available now in Live!

Get 1:1 Help Now