Passing report results as the argument in a custom/user-defined function

[CapeCoder]

I'd like to use the Excel.WorksheetFunction library to compute median
and percentiles in a user-defined 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!

[Ben Eaton]

Just a wild stab in the dark but maybe this isn't working because Avg is a
built in access aggregate function and your custom function isn't.

I'm guessing that if you were to sum the values then perc them it would
work:

=Sum(Perc25([BaseSalary]))

Of course I could have totally misunderstood the problem...

"CapeCoder" <ca*******@capecoder.com> wrote in message
news:b9**************************@posting.google.c om...

I'd like to use the Excel.WorksheetFunction library to compute median
and percentiles in a user-defined 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!