U
Uncle Phil
Guest
It appears that I should be able to use Excel worksheet functions from Access. Just at the moment, I simply want to calculate medians, percentiles and so forth for a recordset selected in a query.
BUT: however I try to call the worksheet function, Access returns "undefined function" or some such message.
I have set references to what I think are the relevant files, including
Microsoft Excel 9.0 object library
Microsft Office web components function library
Microsoft DAO 3.6 object library
I have tried calling the function from within an expression in a query, ie.
ExpressionName:Application.WorksheetFunction.Median(recordsource)
and also setting up as a user-defined function in VB:
Function Percentile(range, Percent)
Percentile = Application.WorksheetFunction.Percentile(range, Percent)
End Function
I've tinkered with both of these for some hours, but got nowhere.
Access help is less than helpful, and VB help not much more so.
Anyone got any clues on this? Don't be too technical please - I'm not a techie, I'm a biologist!
BUT: however I try to call the worksheet function, Access returns "undefined function" or some such message.
I have set references to what I think are the relevant files, including
Microsoft Excel 9.0 object library
Microsft Office web components function library
Microsoft DAO 3.6 object library
I have tried calling the function from within an expression in a query, ie.
ExpressionName:Application.WorksheetFunction.Median(recordsource)
and also setting up as a user-defined function in VB:
Function Percentile(range, Percent)
Percentile = Application.WorksheetFunction.Percentile(range, Percent)
End Function
I've tinkered with both of these for some hours, but got nowhere.
Access help is less than helpful, and VB help not much more so.
Anyone got any clues on this? Don't be too technical please - I'm not a techie, I'm a biologist!