Average in Group Footer

[SaraMegan]

Hi, All!

I'm trying to calculate an average in a group footer in a report. In my underlying query I have calculated a DateDiff, and this is what I want to average. I've tried using an unbound text box and using =Avg([FileToMail]), but it tells me my sql includes an invalid data type.

So I'm figuring that there's something going on because these are calculated fields that haven't really been formally defined as numbers and aren't stored anywhere?

I have searched for this topic (as I always do) in both these forums and MS Access help, and thought I found an answer, but instead found a little more confusion.

Anyone have any ideas?

Thanks in advance. Let me know if I need to clarify more.

--Sara

 

[pono1]

SaraMegan,

Have you tried =DAvg?

=DAvg("FieldNameinQueryUsingDateDiff","QueryName")

Regards,
Tim

 

[SaraMegan]

Hi, Tim

Thanks for the response.

I just tried DAvg:

=DAvg("[FileToMail]","qryDeputy")

...and I get an #Error. (Have also tried without brackets)

Question to you: If I get this right, will it still do what i need and just average the stuff in the previous group? The purpose of the report (generally) is to see how long it takes for a few specific tasks, from start date to end date, per person. So it's grouped by person, and then in the group footer, I want that person's averages.

If I get it right, will DAvg calculate by group, or will it calculate the whole average, since the query shows all results? (Is not by person...)

Thanks again.

--Sara

 

[SaraMegan]

Found solution

Hi, all.

I found a solution. Whether it's the best solution or not, I'm not sure, but it works well so I'm going to stick with it.

What I did was average a re-calculation of the DateDiff:

=Avg(DateDiff("d",[DateFiled],[DateMailed]))

And that does it for me. I also have Decimal set to Zero and Format at Fixed. It even rounds for me!

--Sara

PS. Thanks to Pat Hartman for inadvertently giving me the tip in someone else's post from a while ago.