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

x
?
Solved

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

Posted on 2006-06-22
7
Medium Priority
?
530 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 2000 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
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.
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…

604 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