Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Calling up the Networkdays Excel functions to use in Access Query

Posted on 2004-08-01
8
Medium Priority
?
785 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
6 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
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.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

783 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