"Enter Parameter" problem doing an Avg function

[jeremy.lankenau]

Hi all,

I'm an HR professional, but because I'm "okay" with computers I've been tasked with an HR database that tracks the time it takes to hire a new employee. Help would be appreciated because my attempts to find the answer has proved fruitless so far..

Working on a report that uses the datediff function to determine the number of days between two dates. I have about twelve of these columns, and all are working as they should.

I have three columns, TotalTime, HRTime, and ManagerTime. I get the "enter parameter" box when trying to do an avg function on these columns

For TotalTime control source, I have: =DateDiff("d",[StartDate], [EndDate])

For HRTime I have: =Nz([HR_AppBToSRD],0)+Nz([HR_PDRev],0)+Nz([HR_PDtoDA],0)+Nz([HR_DAtoVA],0)
This adds up the total HR times. I'm using the Nz function since sometimes the dates are null and I still need to know the total days it's been worked. The ManagerTime is very similar to the HRTime.

Now, I get the enter parameter box when I create a text box on the report and set the control source to: = avg ([HR_Time]) . Same is true for TotalTime and ManagerTime. I'm able to do Sum's and Count's no problem (and have IIf's and what not in there as well), but I'm using other fields that don't use the DateDiff function.

From what I've read, the result of the datediff function should return an integer. I've tried using the round function and casting as a str or dbl but I still get the enter parameter box.

I've tried using access help and searching the web but haven't come up with a solution yet, so any help would be greatly appreciated.

Jeremy

 

[pbaldy]

You can't sum (or avg) a calculated control. Try

=Avg(Nz([HR_AppBToSRD],0)+Nz([HR_PDRev],0)+Nz([HR_PDtoDA],0)+Nz([HR_DAtoVA],0))

or

=Avg(DateDiff("d",[StartDate], [EndDate]))

 

[jeremy.lankenau]

pbaldy,

Thanks, that worked for TotalTime but not HR_Time or Manager_Time, probably because those are calculated controls as well. Is this because the report doesn't store the calculated value's? Would it work better if I calculated these times on my table and kept them stored for easier access in reporting later?

Jeremy

 

[pbaldy]

I'm not a fan of storing calculated values in tables, though a calculated field may be appropriate. I'd push the calculations to the query underlying the report, which will make them a field that you can simply sum/avg/whatever.

 

[jeremy.lankenau]

Appreciated. I don't have much experience working with query's yet, but I'll start diving in. It's interesting stuff, and I'm getting paid to take the time to learn it, so I'm happy

 

[pbaldy]

No problem.

 

[jeremy.lankenau]

pbaldy,

I tried your recommendation and created a set of queries that I could use on my report instead of using calculated controls that arent stored.

Example of a query is SELECT DateDiff("d",[DTPosVac],[DTAppB]) AS MGR_VacToAppB
FROM PreRPA;

This query returns the correct values.

However, when I go into the report and change the column from the expression that does the calculated control to =[qry_MGR_VacToAppB]![MGR_VacToAppB] I'm getting the "enter parameter value" again. This doesn't make sense to me as I can run the query independently and it returns the correct values.

Your continued help is much appreciated. I'm learning a lot through this process, though it is painful

 

[pbaldy]

If that query is not the record source of the report, you'd have to use a DLookup() to get the value (or other methods).

=DLookup("MGR_VacToAppB", "qry_MGR_VacToAppB")

If the query returns multiple records, you likely need a criteria, or perhaps that field can be added to the query that is the report's record source.