• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 835
  • Last Modified:

Calling up the Networkdays Excel functions to use in Access Query

I have seen that it is possible to call up Excel Functions in Access.  I want to pull the Networkdays function is Access so I can use it in a query.  I have tried several modules but still can't use the funcition in a query.  Can someone guide me (step-by-step) on what to do?
0
Hermanifer
Asked:
Hermanifer
3 Solutions
 
jadedataMS Access Systems CreatorCommented:
The query will not be able to "see" the excel library during it's run.  You will have to write a user defined function that calls this Networkdays function that can be called from your query.

Excel must be referenced in the project.

Have you been able to successfully call the Excel function in testing?  If not,... what errors are you getting??

-j-

0
 
Rey Obrero (Capricorn1)Commented:
Networkdays
Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays.

to get work 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
-----------------------

check this link for date that fall on a holiday or weekends

HOW TO: Determine If a Date Falls on a Weekend or on a Holiday in Access
http://support.microsoft.com/?kbid=210064


0
 
TextReportCommented:
I use my own version of it, it uses a query called USysqryBankHolidayCount that is based on a table that you can keep a list of bank holidays etc that you want to exclude from your work days. The query has 2 paramteres that are used to filter the query they are date parameters of pdteStartDate and pdteEndDate

Cheers, Andrew

Function TextReport_NetWorkDays(BegDate As Variant, EndDate As Variant, Optional pbooIgnoreHolidays As Boolean) As Integer
Dim db As Database
Dim qd As QueryDef
Dim rst As Recordset

Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer
Dim Holidays As Long

    BegDate = Int(CDate(BegDate))
    EndDate = Int(CDate(EndDate))
   
    If pbooIgnoreHolidays Then
       Holidays = 0
    Else
       Set db = CurrentDb()
       Set qd = db.QueryDefs("USysqryBankHolidayCount")
       qd.Parameters!pdteStartDate = BegDate 'Format(BegDate, "dd/mmmm/yyyy")
       qd.Parameters!pdteEndDate = EndDate 'Format(EndDate, "dd/mmmm/yyyy")
       Set rst = qd.OpenRecordset()
       Holidays = rst!qty
       rst.Close
    End If
   
    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

    TextReport_NetWorkDays = WholeWeeks * 5 + EndDays - Holidays

End Function
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
ala_frostyCommented:
Try this out
Public Function NetworkDays(start_date As Date, end_date As Date, Optional holidays As Variant)
    'Currently does nothing with the holidays
    NetworkDays = DateDiff("d", start_date, end_date) + 1
    NetworkDays = NetworkDays - Int(NetworkDays / 7) * 2
    If DatePart("w", start_date) = vbSunday And DatePart("w", end_date) <> vbSaturday Then
        'Start on Sunday but not ending on Saturday
        NetworkDays = NetworkDays - 1
    ElseIf DatePart("w", start_date) = vbSaturday And DatePart("w", end_date) <> vbFriday Then
        'Start on Saturday and not ending on Friday
        NetworkDays = NetworkDays - IIf(DatePart("w", end_date) = vbSaturday, 1, 2)
    ElseIf DatePart("w", start_date) > vbSunday _
            And DatePart("w", start_date) < vbSaturday _
            And DatePart("w", end_date) = vbSaturday Then
        'Start on a weekday and end on saturday
        NetworkDays = NetworkDays - 1
    End If
   
End Function
0
 
ala_frostyCommented:
When I wrote that function, there were no other responses.

FWIW: My solution doesn't do the loops suggested by Capricorn and TextReport, so no matter how far apart your dates are, it'll always take the same amount of time. I checked it against the EXCEL (NETWORKDAYS) function too.

The only trouble with it right now is that it doesn't handle the holidays. Let us know if that's a problem or not.
0
 
ala_frostyCommented:
Any luck with this yet?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now