Allow for holidays

[msmithtlh]

I have a situation where I need to count workdays between assigned_date and completed_date (excluding weekends and holidays). I have worked out the weekends, but need some help with holidays. I set up a table with the explicit dates for holidays:

HoidayDate
01/01/2014
01/20/2014
05/26/2014
07/04/2014
09/01/2014
11/11/2014
11/27/2014
11/28/2014
12/25/2014

I use datediff to calculate the number of "rawdays" between my two dates. I use "skipdays" to store an integer for days not to be counted (weekends and holidays). When I am through figuring out skipdays, then workingdays will simply be "rawdays" minus "skipdays."

I want to go through the holidays table in a loop, and if assigned_date <= holiday and completed_date >= holiday, then add 1 to skipdays. I am trying to do this in a macro.

I need some help figuring out the proper "action" in the macro - don't see any "loop" words. If I need a different approach, please give specific directions. Thanks!!

[pbaldy]

Don't use macros, but historically they didn't have loops. You can use a DCount() to find the number of holidays between your 2 dates, which would probably be more efficient than a loop anyway.

[ipisors]

I wouldn't suggest using Macros in Access at all. Here is a VBA function I wrote to count DateDiff with option not to count weekends or holidays, depending on what you want to do.

Below:
__________________________________________________ __________________________________________________ _____

Function DateDiffWithExceptions(FirstDate As Date, SecondDate As Date, _
                                strDates As String, _
                                Optional ExcludeWeekends As Boolean = False) As Long
On Error GoTo errhandler
'   strDates must be like this: MMDD|MMDD|MMDD|  etc
'   ex.   0101|0704|1127
'returns the same as DATEDIFF() function does (# of days between 2 dates)
'except it also excludes additional days specified by developer - holidays, etc.
'as long as I was at it I put in an option to exclude weekends or not, as well.
'if omitted, this arguments defaults to false (weekends are not excluded)
Dim dtLooper As Date    'generic date looping variable
Dim lngCount As Long
Dim lngYear As Long
Dim x As Long
Dim blAlreadyDeducted As Boolean
Dim strExceptions() As String
strExceptions = Split(strDates, "|")
lngCount = DateDiff("d", FirstDate, SecondDate)             'start by assigning max count. Entire interval
'eliminate some weird possibilities first, since DateDiff() does allow for negative dates,
'and since the calculation may result in 0 days elapsed, either of which would probably break further steps:
If lngCount <= 0 Then
    DateDiffWithExceptions = lngCount
    Exit Function
End If
dtLooper = FirstDate
Do While dtLooper <= SecondDate                             'loop between dates
    If ExcludeWeekends = True Then
        If (Weekday(dtLooper) = 7 Or Weekday(dtLooper) = 1) Then
            lngCount = lngCount - 1
        End If
    Else
        For x = 0 To UBound(strExceptions) - 1
            If Format(dtLooper, "MMDD") = strExceptions(x) Then 'then remove it due to holiday exception, and move on
                lngCount = lngCount - 1
            End If
        Next x
    End If
    
    'then go to next date in the interval analysis
    dtLooper = dtLooper + 1
    
Loop
DateDiffWithExceptions = lngCount
Exit Function
errhandler:
DateDiffWithExceptions = 999999999 'use this in other code to be able to recognize, react if an error occurs
Exit Function
End Function

[msmithtlh]

Pbaldy,

DCount sounds like the way to go, but I'm having some trouble with syntax.

The 2 dates are in one table ([Tempdays]![DateAssigned] and [Tempdays]![DateCompleted]), the holidays are in another table: [Holidays]![HolidayDate] . I've looked up the function, but I'm missing something.

Function CntHolidays()

intx = Dcount("Holidaydate","Holidays", between [Tempdays]![DateAssigned]and [Tempdays]![DateCompleted])

End Function

I get an error messsage "The expression contains an ambifuous name. Verify that each name in the expression refers to a unique object."

Could you please help with this? Also, once I get the value for intx, I want to add it to [Tempdays]![Skipdays]. Would I do that in the function?

Thanks!!

[June7]

Is this expression in VBA code behind form with the DateAssigned and DateCompleted and Skipdays fields in RecordSource? What event?

Try:

Me!Skipdays = DCount("Holidaydate","Holidays", "Holidaydate BETWEEN #" & Me!DateAssigned & "# AND #" & Me!DateCompleted & "#")

[msmithtlh]

I liked the idea of both suggestions but kept having trouble with syntax. I finally wrote a series of queries that gave me the needed results. Thanks.