Solved

Calling up the Networkdays Excel functions to use in Access Query

Posted on 2004-08-01
8
721 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 119

Assisted Solution

by:Rey Obrero
Rey Obrero 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
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.
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…

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now