NETWORKDAYS for Access (1 Viewer)

[Autoeng]

Found this on another site as to how to use NETWORKDAYS in Access.

There is a NetWorkDays function available to Excel worksheets but not directly available in Microsoft Access. You may have noticed that this function is listed in Microsoft Access help. This
is because we included the Excel help topics in case users were using the Excel spreadsheet components within Data Access Pages in Access 2000.

In any case, the NetWorkDays function is exposed in the Microsoft Office WebComponents function library (MSOWCF.Dll), and it is possible to reference this library from Microsoft Access and call this function indirectly by using your own function.

In order to use this particular function, follow these steps:

1. Insert a new module into your Microsoft Access database.

2. On the Tools menu, click References.

3. Scroll down through the list, and check the checkbox next to "Microsoft Office Web Components Function Library." If it is not in the list, then click the Browse button, and locate MSOWCF.Dll
which should be in your C:\Program Files\Microsoft Office\Office folder.

4. Click OK to close the References dialog box.

5. Insert the following code into the module you created in step 1.

Function GetNetWorkDays(startDate As Date, endDate As Date) As Integer

Dim objFunction As MSOWCFLib.OCATP
Set objFunction = New MSOWCFLib.OCATP
GetNetWorkDays = objFunction.NETWORKDAYS(startDate, endDate)
Set objFunction = Nothing
End Function


This code allows you to indirectly call the NetWorkDays function from within Microsoft Access. You can call the GetNetWorkDays function, pass it the starting and ending dates, and then return the value from the NetWorkdays function.

If you want to call the function from a control on a Microsoft Access form or report, you would use the following syntax on the control's ControlSource property:

ControlSource: =GetNetWorkDays(#12/1/1999#, #12/17/1999#)

Of course, this example is using hard coded dates. You will need to replace the dates in the expression above with the dates you wish to calculate, or a reference to a field in your table which
contains them.

For instance, ControlSource: =GetNetWorkDays([StartDate], [EndDate])

 

[Autoeng]

OK. So what, I found it. Now how do I use it when the module doesn't define the holiday range?

How do I change the module to find the holiday range located in tblHoliday.Holiday and would it be possible to change the week count (for a four day week Mon - Thurs)?

Autoeng

 

[Freshman]

<Bump>

I know if is an old post but I noticed there is no answer to this and might be intresting to know if it is possible to structure it like you do in Excel

OK. So what, I found it. Now how do I use it when the module doesn't define the holiday range?

How do I change the module to find the holiday range located in tblHoliday.Holiday and would it be possible to change the week count (for a four day week Mon - Thurs)?

Autoeng

 

[G37Sam]

You can download the dll from here: http://www.dlldump.com/download-dll-files.php/dllfiles/M/MSOWCF.DLL/download.html

I just tried it and it worked perfectly

 

[Freshman]

Hi, that for that.
I found that I could actually refrence it without the dll file as the object was already in the list. Just had to tick it.

But can you give me an example of the syntax please as I have not idea. I tried it like Excel but not working.

Thanks

 

[G37Sam]

First you define the below function in a module

Function GetNetWorkDays(startDate As Date, endDate As Date) As Integer
Dim objFunction As MSOWCFLib.OCATP
Set objFunction = New MSOWCFLib.OCATP
GetNetWorkDays = objFunction.NETWORKDAYS(startDate, endDate)
Set objFunction = Nothing
End Function

Then you call the function, let's assume you want to find out the number of working days left this year

msgbox GetNetWorkDays(Date,#31/12/2013#)

 

[Freshman]

Hi there,

Thanks, but what I actually want to know is how to refer to the table containing the list of holidays like you do in Excel by referring to the range containing the same.

Without being able to do that, the function is not worth much to me.

Thanks again
Pierre

 

[G37Sam]

Well you can count the number of working days and subtract the holidays from it?

Have a table that defines the holiday dates like the below:

HolidayID - HolidayDate
1 - 12/25/2012
2 - 1/1/2013

Then do

Dim DaysToSubtract as integer
DaysToSubtract = DLookup("count(*)", "holidays", "holidayDate between #" & Date & "# AND #01-Jan-2013#")

 

[Freshman]

Thanks - yes I'm actually doing something like that already (counting the number of holidays within the same date range) but I thought this "method" of being able to use Excel function would make things much easier but it is not really as flexible as it is in Excel. Not that that will EVER make me an Excel fan Access still rules.

 

[mac.vba]

Hello,

I'm using the same code in msaccess which used to work perfectly on the Win XP platform. I upgraded my OS to Win 7. It's now giving an error as "Runtime Error 429 - ActiveX Component Can't Create Object". I unchecked & rechecked all the library references.

Old OS: Win XP
New: Win 7
Old msaccess: 2003
New: 2010
Old office: 2003
New: 2010

Please help.

 

[michaeljryan78]

you may want to take a look at something I just came up with. test it to make sure. It requires a holiday table tblHolidays with a field HolDate (as datetime, shortdate).

Function GetWorkDays(dtDateIn As Date, dtDateOut As Date) As Integer
Dim x As Integer
Dim rs As Recordset
Dim db As Database
Dim strSQL As String
Dim dtIncrement As Date
strSQL = "Select holDate from tblHolidays"
x = 0
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
dtIncrement = dtDateIn
Do Until dtIncrement = dtDateOut
 
    Select Case Weekday(dtIncrement)
        Case vbSaturday
            x = x
        Case vbSunday
            x = x
            
        Case Else
            rs.FindFirst ("holDate = # " & Format(dtIncrement, "mm/dd/yyyy") & "#")
            If rs.NoMatch = True Then
            x = x + 1
            End If
    End Select
    dtIncrement = dtIncrement + 1
Loop
GetWorkDays = x
End Function

 

[michaeljryan78]

I forgot to close the rs at the end and set objects to nothing... Opps!

 

[mjseim]

I made some tweaks for my own use. I made the table and field parameters. I also handled the direction of the dates (first>last, last>first). Take care.

Function NetWorkDays(dtDateIn As Date, _
                     dtDateOut As Date, _
                     strHolidayTable As String, _
                     strHolidayField As String) As Integer

    Dim x As Integer
    Dim rs As Recordset
    Dim strSQL As String
    Dim dtIncrement As Date
    Dim lngDateDirection As Long

    If dtDateIn < dtDateOut Then
        lngDateDirection = 1
    Else
        lngDateDirection = -1
    End If

    strSQL = "Select * from [" & strHolidayTable & "]"
    x = 0
    Set rs = CurrentDb.OpenRecordset(strSQL)
    dtIncrement = dtDateIn

    Do Until dtIncrement = dtDateOut

        Select Case Weekday(dtIncrement)

            Case vbSaturday
                x = x

            Case vbSunday
                x = x

            Case Else
                rs.FindFirst ("[" & strHolidayField & "] = #" & Format(dtIncrement, "mm/dd/yyyy") & "#")
                If rs.NoMatch = True Then
                    x = x + 1
                End If
        End Select

        dtIncrement = dtIncrement + (1 * lngDateDirection)

    Loop

    NetWorkDays = x * lngDateDirection

    Set rs = Nothing

End Function

 

[mac.vba]

Can we have the code without holidays table? I do not have one.


In addition, can I have this one also solved pleaseeeeeeeeeeeeeeeee

http://www.access-programmers.co.uk/forums/showthread.php?p=1281562#post1281562

:banghead:

 

[furstukin]

There is actually a much simpler solution to using Excel functions in Access. Simply add the reference to the Microsoft Excel xx Object Library by going to the VBA Project Explorer (ALT + F11) and the Tools>References and finding the Microsoft Excel Object Library and marking the tick box next to it.

Then you just create a function like this:

Function GetNetWorkDays(startDate As Date, endDate As Date) As Integer
GetNetWorkDays = WorksheetFunction.NETWORKDAYS(startDate, endDate)
End Function

And you can easily test it with a form using this code:

Private Sub Form_Current()
MsgBox GetNetWorkDays(Date, #12/31/2013#)
End Sub

Note this method works for all functions in Excel not just the NETWORKDAYS one.

 

[Uncle Gizmo]

Using the excel function is mentioned in the first post. You realise this is an old thread don't you?

Sent from my SM-G925F using Tapatalk

 

[furstukin]

Using the excel function is mentioned in the first post. You realise this is an old thread don't you?

Sent from my SM-G925F using Tapatalk

Old post or not it is still relevant and people find it using search engines all the time. However, you apparently you did not read my post as it had nothing to do with the function it has to do with not needing a .dll that may not exist on your PC and in my case I don't have admin rights at work to install the .dll to use anyhow but the Excel Object Library does the same thing and is built in. But I digress.

 

[ugadawgs306]

What about the holidays part of the NETWORKDAYS function? I can't seem to get it to work calling the Excel function in Access. It works fine without the holiday dates.

I have a table (tblHolidays) with all holiday dates (HolDates) listed. How do I use the NETWORKDAYS function and look at my table to use the holiday dates?

Thanks!!

 

[cheefai7]

What about the holidays part of the NETWORKDAYS function? I can't seem to get it to work calling the Excel function in Access. It works fine without the holiday dates.

I have a table (tblHolidays) with all holiday dates (HolDates) listed. How do I use the NETWORKDAYS function and look at my table to use the holiday dates?

Thanks!!

Yes, I was going through the solution and I need to incorporate a public holiday table. How should I write in the VBA?