Date Calculations + Average based on specific field entry on a report

[jeremy.lankenau]

Hi all,

I've currently got a report called rpt_TrackedTime that is supposed to keep track of cases that have been worked by the employees. I'm trying to have the report do some averages of the data from my main table (PreRPA) in the report. It isn't working and I can't do a calculated control in the table itselt (won't allow me) or in a query. How can I get over this hurdle?

A good example is this:

I have this to calculate an average in the report -

=DAvg("DateDiff('d',[DTPosVac],[DTRPARecPreWorkComplete])","PreRPA","[District]= 'SWDO' And [ActionOngoingComplete] = 'Complete'")

PreRPA is a table that has these other fields. I'm trying to do a date differential off of it but only for a specific District who cases are Complete. The report, rpt_TrackedTime is saying "#Error" for the entire report, or based on a date range that a user can put in a form and generate the report for that specific timeframe.

I haven't had any error's for calculations for a specific row on the report so I don't know why I can't do a calculation on them all. An example of a row calculation within the report is:

Total Time:
=IIf([ActionOngoingComplete]="Complete",[CPAC_Time]+[MGR_Time])
These fields are within the report and the expression for them is:
CPAC_Time (all referenced fields are within the report):
=Nz([CPAC_AppBToSRD],0)+Nz([CPAC_PDRev],0)+Nz([CPAC_PDtoDA],0)+Nz([CPAC_DAtoVA],0)
MGR_Time (all referenced fields are within the report):
=Nz([MGR_VacToAppB],0)+Nz([MGR_PDRev],0)+Nz([MGR_DA],0)+Nz([MGR_VA],0)+Nz([MGR_Hold],0)+Nz([MGR_RPA],0)

So bottom line is I can play around with the rows with no issues on a report but when I try to do a calculation by either examining data in a column on the report or looking at doing a calc for all rows in a table, I run into issues.

Any help is greatly appreciated!

 

[arnelgp]

if it is not possible in expression, can you not do it in the report's load event.
open the report's recordsource.
loop through each record and get the calculation.
assign the calculated value to a textbox control on your report.

 

[plog]

You lost me somewhere in your explanation so I don't know the big picture of what you are trying to do, but I do see an error in your DAvg:

=DAvg("DateDiff('d',[DTPosVac],[DTRPARecPreWorkComplete])","PreRPA","[District]= 'SWDO' And [ActionOngoingComplete] = 'Complete'")

The first argument of DAvg, is the field you want to average. I highly doubt there is a field called "DateDiff('d',[DTPosVac],[DTRPARecPreWorkComplete])" in PreRPA. I don't know exactly what you hope to do with that DateDiff, but it shouldn't be the first argument of the DAvg. Most likely it should be in the last argument which is where criteria goes.