Excel Networkdays function in Access

[RichardMGreen]

Hi all

I need to replicate the Excel function Networkdays in Access.
I've come across some VBA code but I'd rather do it using queries if I can.

Is it possible to do this in a query or do I definitely need code?

 

[Joe4]

It is pretty complex, I have never seen it done without VBA (I'm not saying it isn't possible, but it would probably be very complex -- some things just lend themselves to work better in VBA).

However, using VBA isn't nearly as daunting as it may seem. All that you are doing is copying a User Defined Function into a module that you can use in your queries. So once you copy the NetWorkDays function into a VBA module, you probably will never have need to reference or edit it. You would just use the function like you would any other native function in queries.

 

[RichardMGreen]

Thanks for that. I'll grab some code and see how I get on.

 

[RichardMGreen]

OK, slight hitch.
I've put the code into an Access code module and adjusted the queries to suit. It works OK in Access.
I'm actually running the queries from Excel using an ADODB connection.
When it reaches the queries that use the UDF, I get an error that says:-
"Undefined function 'WorkingDays' in expression".

I think it's because Excel doesn't recognise the VBA.

Anyone any ideas?

 

[SydneyGeek]

If you are pulling the data into Excel, use NetworkDays when it gets there. Excel usually spits the dummy when it encounters an Access function (custom or otherwise).
You *can* use Excel functions in Access (by referencing the Excel library), which might be an option, but I don't know how well that would survive the round-trip.

Denis

 

[RichardMGreen]

I need to get the number of working days before the data gets to Excel. If I don't, the number of rows becomes prohibitive for doing the count further down the line.

Any idea how I would use the Excel function in Access (never tried that before)?

 

[RichardMGreen]

Hi all

This is my code so far:-

Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer
    Dim Exl As Object
    Set Exl = CreateObject("Excel.Application")
    WorkingDays = Exl.WorksheetFunction.NetWorkdays(StartDate, EndDate)
    Set Exl = Nothing
End Function

It's erroring out on this line:-
WorkingDays = Exl.WorksheetFunction.NetWorkdays(StartDate, EndDate)

with a message that states:-
Object doesn't support this property or method.

I'm assuming it's something to do with the Networkdays function but I'm not sure what.

Anyone any ideas?

 

[SydneyGeek]

It could be a wild goose chase because you usually create an Access function that calls it...
Just wondering, have you tried a make-table query that takes the Access query and dumps it into a temporary table? If that can be run from Excel (using Execute), that would be one option.

Otherwise, you will need to replicate the logic of NetworkDays using several expressions, then summing the result.

Something like --

SELECT tblDates.StartDate, tblDates.EndDate, [EndDate]-[StartDate]+1 AS AllDays, Weekday([StartDate]) AS StartWeekday, Int([Alldays]/7) AS Weeks, [Alldays] Mod 7 AS Remainder, [Weeks]*2+IIf([Remainder]+[StartWeekDay]>7,2,IIf([Remainder]+[StartWeekDay]>6,1,0)) AS WeekEndDays, [AllDays]-[WeekEndDays] AS WorkDays
FROM tblDates;

Note -- the WeekendDays field is not quite right. It needs correction if the first day is a Saturday or Sunday.
But I was able to pull the query into Excel using MS Query and ADO.

denis

 

[RichardMGreen]

AFAIK, the start date will never be on a weekend so that shouldn't matter.
I'll be using that as a subquery for something else so I'll let you know how I get on.

 

[RichardMGreen]

OK, this is my updated query (to use the fields I want):-

SELECT 
qry_Monthly_First_Contacts.Year, 
qry_Monthly_First_Contacts.ClientId, 
qry_Monthly_First_Contacts.[Date of Arrival] AS Start_Date, 
qry_Monthly_First_Contacts.First_Contact AS End_Date, 
qry_Monthly_First_Contacts!First_Contact-qry_Monthly_First_Contacts![Date of Arrival]+1 AS AllDays, 
Weekday(qry_Monthly_First_Contacts![Date of Arrival]) AS StartWeekday, 
Int([Alldays]/7) AS Weeks, 
[Alldays] Mod 7 AS Remainder, 
[Weeks]*2+IIf([Remainder]+[StartWeekDay]>7,2,IIf([Remainder]+[StartWeekDay]>6,1,0)) AS WeekEndDays, 
[AllDays]-[WeekEndDays] AS WorkDays
FROM qry_Monthly_First_Contacts;

The only problem I have now is that it asks for the month and year parameters several times instead of just once.
The initial query this is built up from only needs the parameters once, as does the next query in the sequence. This query is third in line and wants the parameters 4 times before it will bring back any results.

Any ideas why?