Network Days module in Access not working as expected

[phaniakella]

Dear Experts

I have followed the steps as mentioned in below thread

msdn.microsoft.com/en-us/library/dd327646(v=office.12).aspx#Access2007CountingWorkingDays_WorkingDaysDefined

But I am not getting correct results of networking days. Please guide me on where I am going wrong. Thanks in advance.

START_DATE END_DATE NetWorkDays
04/01/2016 11/01/2016 1
11/01/2016 18/01/2016 6
18/01/2016 25/01/2016 6
25/01/2016 01/02/2016 6
01/02/2016 08/02/2016 0
08/02/2016 15/02/2016 6
15/02/2016 22/02/2016 6
22/02/2016 29/02/2016 6
29/02/2016 07/03/2016 1
07/03/2016 14/03/2016 6
14/03/2016 21/03/2016 5
21/03/2016 28/03/2016 4
28/03/2016 04/04/2016 5
04/04/2016 11/04/2016 1
11/04/2016 18/04/2016 6
18/04/2016 25/04/2016 6
25/04/2016 02/05/2016 6
02/05/2016 09/05/2016 -2
09/05/2016 16/05/2016 6
16/05/2016 23/05/2016 6
23/05/2016 30/05/2016 5
30/05/2016 06/06/2016 5
06/06/2016 13/06/2016 6
13/06/2016 20/06/2016 6
20/06/2016 27/06/2016 6
27/06/2016 04/07/2016 6
04/07/2016 11/07/2016 1
11/07/2016 18/07/2016 6
18/07/2016 25/07/2016 6
25/07/2016 01/08/2016 6
01/08/2016 08/08/2016 -1
08/08/2016 15/08/2016 5
15/08/2016 22/08/2016 6
22/08/2016 29/08/2016 5
29/08/2016 05/09/2016 6
05/09/2016 12/09/2016 1
12/09/2016 19/09/2016 6
19/09/2016 26/09/2016 6
26/09/2016 03/10/2016 6
03/10/2016 10/10/2016 -2
10/10/2016 17/10/2016 6
17/10/2016 24/10/2016 6
24/10/2016 31/10/2016 6
31/10/2016 07/11/2016 6
07/11/2016 14/11/2016 3
14/11/2016 21/11/2016 6
21/11/2016 28/11/2016 6
28/11/2016 05/12/2016 6
05/12/2016 12/12/2016 1
12/12/2016 19/12/2016 6
19/12/2016 26/12/2016 4
26/12/2016 02/01/2017 5

 

[arnelgp]

please try this one:

 

[phaniakella]

Hi arnelgp

Thanks a lot. This is working perfectly. Can you please let me know what you have done to work this and where I am going wrong.

Thanks
Phani Akella

 

[Gasman]

please try this one:

Arnelgp,

Thank you for the code.
It failed with me as I was trying to get it to work with some other code from another Access expert, and was not sure where he would have used his fieldnames for the actual holiday date.

Consequently I amended your code to also allow for a different date field in the holiday table?

Option Compare Database
Option Explicit
Public Function Workdays(ByRef startDate As Date, _
     ByRef endDate As Date, _
     Optional ByRef strHolidays As String = "Holidays", _
     Optional ByRef strHolField As String _
     ) As Integer
    ' Returns the number of workdays between startDate
    ' and endDate inclusive.  Workdays excludes weekends and
    ' holidays. Optionally, pass this function the name of a table
    ' or query as the third argument, and the holiday date fieldname as the fourth argument. If you don't the default
    ' is "Holidays" and Holiday
    ' Created by Arnelgp from http://www.access-programmers.co.uk/forums
    On Error GoTo Workdays_Error
    Dim nWeekdays As Integer
    Dim nHolidays As Integer
    Dim strWhere As String
    
    ' DateValue returns the date part only.
    startDate = DateValue(startDate)
    endDate = DateValue(endDate)
    
    nWeekdays = Weekdays(startDate, endDate)
    If nWeekdays = -1 Then
        Workdays = -1
        GoTo Workdays_Exit
    End If
    
    strWhere = "[" & strHolField & "] >= #" & startDate _
        & "# AND [" & strHolField & "] <= #" & endDate & "# " & _
        "And WeekDay([" & strHolField & "])<>1 And Weekday([" & strHolField & "])<>7"
    
    ' Count the number of holidays.
    nHolidays = DCount(Expr:="[" & strHolField & "]", _
        Domain:=strHolidays, _
        Criteria:=strWhere)
    
    Workdays = nWeekdays - nHolidays
    
Workdays_Exit:
    Exit Function
    
Workdays_Error:
    Workdays = -1
    MsgBox "Error " & Err.Number & ": " & Err.Description, _
        vbCritical, "Workdays"
    Resume Workdays_Exit
    
End Function

Public Function Weekdays(ByRef startDate As Date, _
    ByRef endDate As Date _
    ) As Integer
    ' Returns the number of weekdays in the period from startDate
    ' to endDate inclusive. Returns -1 if an error occurs.
    ' If your weekend days do not include Saturday and Sunday and
    ' do not total two per week in number, this function will
    ' require modification.
    On Error GoTo Weekdays_Error
    
    ' The number of weekend days per week.
    Const ncNumberOfWeekendDays As Integer = 2
    
    ' The number of days inclusive.
    Dim varDays As Variant
    
    ' The number of weekend days.
    Dim varWeekendDays As Variant
    
    ' Temporary storage for datetime.
    Dim dtmX As Date
    
    ' If the end date is earlier, swap the dates.
    If endDate < startDate Then
        dtmX = startDate
        startDate = endDate
        endDate = dtmX
    End If
    
    ' Calculate the number of days inclusive (+ 1 is to add back startDate).
    varDays = DateDiff(Interval:="d", _
        date1:=startDate, _
        date2:=endDate) + 1
    
    ' Calculate the number of weekend days.
    varWeekendDays = (DateDiff(Interval:="ww", _
        date1:=startDate, _
        date2:=endDate) _
        * ncNumberOfWeekendDays) _
        + IIf(DatePart(Interval:="w", _
        Date:=startDate) = vbSunday, 1, 0) _
        + IIf(DatePart(Interval:="w", _
        Date:=endDate) = vbSaturday, 1, 0)
    
    ' Calculate the number of weekdays.
    Weekdays = (varDays - varWeekendDays)
    
Weekdays_Exit:
    Exit Function
    
Weekdays_Error:
    Weekdays = -1
    MsgBox "Error " & Err.Number & ": " & Err.Description, _
        vbCritical, "Weekdays"
    Resume Weekdays_Exit
End Function