Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Calling up the Networkdays Excel functions to use in Access Query

Posted on 2004-08-01
8
Medium Priority
?
773 Views
Last Modified: 2008-02-01
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
Comment
Question by:Hermanifer
[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
8 Comments
 
LVL 32

Accepted Solution

by:
jadedata earned 672 total points
ID: 11688608
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
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 664 total points
ID: 11688616
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
 
LVL 28

Assisted Solution

by:TextReport
TextReport earned 664 total points
ID: 11688638
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 7

Expert Comment

by:ala_frosty
ID: 11688780
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
 
LVL 7

Expert Comment

by:ala_frosty
ID: 11688809
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
 
LVL 7

Expert Comment

by:ala_frosty
ID: 11837707
Any luck with this yet?
0

Featured Post

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.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

688 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