Solved

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

Posted on 2006-06-22
7
517 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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
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.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

757 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