Excel.WorksheetFunction Argument in User-Defined Function

[CapeCoder]

I'd like to use the Excel.WorksheetFunction library to compute median
and percentiles in a user-defined function. Rather than creating a
SQL dataset in the function, I'd like to use the data from the calling
report as the function argument.

Example:
The report's Record Source is a query that fetches salaries from a
database, "SELECT JobID, JobTitle, BaseSalary ORDER BY JobTitle".

The report groups BaseSalary by JobTitle.

I have a textbox with Control Source of "=Avg([BaseSalary])"
I have another textbox with Control Source of "=Perc25([BaseSalary])",
where Perc25 is the custom function that calls
Excel.WorksheetFunction.Percentile

The first textbox, which uses the built-in function "Avg", correctly
computes Average using all of the BaseSalary values for that
particular JobTitle.

The second textbox only displays the value of Perc25 for the last
listed BaseSalary. Is there a way to reference the complete set of
BaseSalary values for that particular JobTitle?

Thank you for your help!

[DCM Fan]

The AVG function is a domain aggregate function builtin to Access, so it
already "knows" that every value has to be addressed.

A Custom function cannot do this, unless you can describe Perc25 as a
combination of row-level functions and aggregates (for example, calculating the
GEOMEAN is possible in Access by using the currect LOG and AVG operations)

I'm not familiar with the Perc25 function, so I'm not sure of the nature of its
argument definition. If it's like most XL functions, it either takes a range of
values, or a maximum of 30 comma-delimited values.

You may be better-off creating an equivalent function using Recordsets in
Access, passing in the JobCode, but this will be slow.

If your recordsets are large (many records per JobCode), then maybe using
automation is the way to go: Use CopyFromRecordset out to XL; put the Perc25
function at the bottom of the range; then read back that value.