The following code works in Office 12.0, but I'm using Office 11.0. Is there
some difference in the way that function must be called between the two
functions? The MsgBox does display the median (from
http://support.microsoft.com/?id=153748), but errors on the next line with
"Run-time error '438': Object doesn't support this property or method"

Public Function NetWorkdays(StartDate As Date, EndDate As Date) As Long
Dim obj As Excel.Application, WorkDays As Long
Set obj = CreateObject("Excel.Application")
WorkDays = obj.Application.NetWorkdays(StartDate, EndDate, Holidays())
MsgBox obj.Application.Median(1, 2, 5, 8, 12, 13)
obj.Quit
Set obj = Nothing
NetWorkdays = WorkDays
End Function

Function Holidays() As Date()
Dim rs As Recordset, counter As Long
Set rs = CurrentDb.OpenRecordset("Holidays")
Dim hd(100) As Date
Do While rs.EOF = False
hd(counter) = rs("Holidate")
rs.MoveNext
counter = counter + 1
Loop
Holidays = hd
End Function

Ooops, sorry for any confusion -- the code should be this (the median
function works and the error occurs on the following NetWorkdays function):

Public Function NetWorkdays(StartDate As Date, EndDate As Date) As Long
Dim obj As Excel.Application, WorkDays As Long
Set obj = CreateObject("Excel.Application")
MsgBox obj.Application.Median(1, 2, 5, 8, 12, 13)
WorkDays = obj.Application.NetWorkdays(StartDate, EndDate, Holidays())
obj.Quit
Set obj = Nothing
NetWorkdays = WorkDays
End Function

Function Holidays() As Date()
Dim rs As Recordset, counter As Long
Set rs = CurrentDb.OpenRecordset("Holidays")
Dim hd(100) As Date
Do While rs.EOF = False
hd(counter) = rs("Holidate")
rs.MoveNext
counter = counter + 1
Loop
Holidays = hd
End Function

Don't bother going through Excel.

For the equivalent of the NetWorkdays function, see my September, 2004
"Access Answers" column in Pinnacle Publcation's "Smart Access" You can
download the column (and sample database) for free from
http://www.accessmvp.com/djsteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Don W" <(E-Mail Removed)> wrote in message
news:216EC7C6-E6F6-49B0-AF3F-(E-Mail Removed)...
> Ooops, sorry for any confusion -- the code should be this (the median
> function works and the error occurs on the following NetWorkdays
> function):
>
> Public Function NetWorkdays(StartDate As Date, EndDate As Date) As Long
> Dim obj As Excel.Application, WorkDays As Long
> Set obj = CreateObject("Excel.Application")
> MsgBox obj.Application.Median(1, 2, 5, 8, 12, 13)
> WorkDays = obj.Application.NetWorkdays(StartDate, EndDate, Holidays())
> obj.Quit
> Set obj = Nothing
> NetWorkdays = WorkDays
> End Function
>
> Function Holidays() As Date()
> Dim rs As Recordset, counter As Long
> Set rs = CurrentDb.OpenRecordset("Holidays")
> Dim hd(100) As Date
> Do While rs.EOF = False
> hd(counter) = rs("Holidate")
> rs.MoveNext
> counter = counter + 1
> Loop
> Holidays = hd
> End Function
>
>