Pass report's recordsource value to DAvg function call

[NZSchoolTech]

I have a report that gets data from a query. In the footer of the report
I have a number of calculated fields that work with the same recordset
to display numerical calculation results. Each of these uses a DAvg
function call with specific criteria to filter out some of the data from
the recordset.

Ideally when we do calculated fields with criteria on a report it would
be nice to have the fields simply use the data that has already been fed
into the Detail section of the report. This seems so obvious that I
cannot understand why it appears that this is not provided for in
Access. In a simplified way it is provided for as a Group footer option
but without the flexibility that the domain aggregate functions give us
for criteria filtering.

If that cannot be done then I would like to know the VBA code which I
can specific in the ControlSource property of the calculated field, that
can read the Recordset property of the report and pass the value of that
property in the DAvg function call.

To make it a little more complicated but versatile I want it to
automatically know the name of the report it is in. I tried using Me to
get this but couldn't make it work.

For example in a typical field's ControlSource property it has something
like

=DAvg("[somefield]","[somequery]","somecriteria")

Whereas ideally it would look something like

=DAvg("[somefield]",Me![RecordSource].Value, "somecriteria")

(I have tried exactly that and variations but only got errors, so it
must be wrong)
TIA

 

[Wolfgang Kais]

Hello "NZSchoolTech".

I have a report that gets data from a query. In the footer of the
report I have a number of calculated fields that work with the same
recordset to display numerical calculation results. Each of these
uses a DAvg function call with specific criteria to filter out some
of the data from the recordset.

Ideally when we do calculated fields with criteria on a report it
would be nice to have the fields simply use the data that has already
been fed into the Detail section of the report. This seems so obvious
that I cannot understand why it appears that this is not provided for
in Access. In a simplified way it is provided for as a Group footer
option but without the flexibility that the domain aggregate
functions give us for criteria filtering.

Right, domain aggregtate functions do not care of the environment, you
will have to use SQL aggregate functions to achieve this.

If that cannot be done then I would like to know the VBA code which
I can specific in the ControlSource property of the calculated field,
that can read the Recordset property of the report and pass the value
of that property in the DAvg function call.

You mean the [RecordSource] propety?

To make it a little more complicated but versatile I want it to
automatically know the name of the report it is in. I tried using
Me to get this but couldn't make it work. For example in a typical
field's ControlSource property it has something like

=DAvg("[somefield]","[somequery]","somecriteria")

Whereas ideally it would look something like

=DAvg("[somefield]",Me![RecordSource].Value, "somecriteria")

(I have tried exactly that and variations but only got errors, so it must
be wrong)

"Me" can only be used in VBA, not in an expression in a calculated
field. But usually, the ojects (forms and reports) "know themselves",
so try =DAvg("[somefield]",[RecordSource],"somecriteria")

This will still cause "wrong" results, when the report is opened usig
a filter or a "where condition".

I think it's best to use SQL aggregate functions, and it should be
possible using a cinstruction like this:
=Avg(Iif(somecriteria,[somefield],Null))

 

[NZSchoolTech]

"Me" can only be used in VBA, not in an expression in a calculated
field. But usually, the ojects (forms and reports) "know themselves",
so try =DAvg("[somefield]",[RecordSource],"somecriteria")

This will still cause "wrong" results, when the report is opened usig
a filter or a "where condition".

I think it's best to use SQL aggregate functions, and it should be
possible using a cinstruction like this:
=Avg(Iif(somecriteria,[somefield],Null))

Thanks, that has saved a lot of hair especially as the DAvg fields
stopped working when I changed my source query.

 

[xiaojun]

"Me" can only be used in VBA, not in an expression in a calculated
field. But usually, the ojects (forms and reports) "know themselves",
so try =DAvg("[somefield]",[RecordSource],"somecriteria")

This will still cause "wrong" results, when the report is opened usig
a filter or a "where condition".

I think it's best to use SQL aggregate functions, and it should be
possible using a cinstruction like this:
=Avg(Iif(somecriteria,[somefield],Null))

Thanks, that has saved a lot of hair especially as the DAvg fields stopped
working when I changed my source query.