How do I average a calculated field in a report?

[Guest]

I have a field named Date of Birth, one named Date Today, a third named Age.
The age is calculated from the first two. How do I sum this calculated age
total to average it by the number of records. Access 2003.

 

[Duane Hookom]

You can sum the calculation used for the age. You can't sum the control.

 

[Guest]

How do I sum the calculation for the age? =Sum(Age) produces 0 as a result.

 

[Douglas J. Steele]

I believe Duane's assuming (as am I) that when you said that "the age is
calculated from the first two", you meant you have a formula as the control
source for the Age text box. You need to put that formula in your Sum
statement, not a reference to the text box.

 

[Guest]

When I apply the SUM to the calculation, Access informs me that I cannot use
an Aggregate with my expression:
=DateDiff("d",[BirthDate],[DateToday])/Count([PatientNumber]). What did I do
wrong?

 

[Guest]

Access informs me that I cannot use an Aggregate function such as SUM with my
expression: =DateDiff("d",[BirthDate],[DateToday])/Count([PatientNumber]).
What have I done wrong?

 

[Duane Hookom]

There is a function to calculate age at
http://www.mvps.org/access/datetime/date0001.htm. Then you can show the
average age in a group or report header or footer with:

=Avg(Age([Date of Birth],[Date Today]))

--
Duane Hookom
MS Access MVP
--

Access informs me that I cannot use an Aggregate function such as SUM with
my
expression:
=DateDiff("d",[BirthDate],[DateToday])/Count([PatientNumber]).
What have I done wrong?

I believe Duane's assuming (as am I) that when you said that "the age is
calculated from the first two", you meant you have a formula as the
control
source for the Age text box. You need to put that formula in your Sum
statement, not a reference to the text box.

 

[Guest]

Thank you very much. It took a bit of tinkering, but it calculates well now.