Solved

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

Posted on 2006-06-22
7
520 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 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

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…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

813 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