Solved

Calling up the Networkdays Excel functions to use in Access Query

Posted on 2004-08-01
8
729 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
8 Comments
 
LVL 32

Accepted Solution

by:
jadedata earned 168 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 166 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 166 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How Can I Grab This Information off a PDF Form 23 49
VBA Access 2016 syntax 6 43
Criteria for Date for DCount 4 24
Sum with where criteria on a report 5 13
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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…

777 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