Showing Query Calculated Fields in Reports

[Doug]

I have a query that calculates the current age for each of
my cows, comparing birthdate with today's date (field
name: Current Age). When I generate a report based on this
query, the Current Age field in the report is blank. I
have tried recreating the calculation within the report
itself, but have had no success. This query and others
have made some useful data through their calculations, but
now I need to know how to use that data as data, especially
in reports. Can someone help me?

 

[Jeff Boyce]

Doug

I have a function that calculates current age, based on date of birth. I
use this function in a query, to provide a value for a CurrentAge, then use
that CurrentAge field in a report. I am not running into a problem.

How is what you are doing different from my description above?

More info, please...

Jeff Boyce
<Access MVP>

 

[Doug]

I have a query with two fields: date of birth and current
date (entered manually; I tried Now (), but I could not get
an accurate calculation). A third field gives me current
age in days. I then have a second,separate query that
pulls this field current age in days, and converts it then
to age in years. In the first query, the current age in
days results from this expression: Expr 1:
[AgeCalcDate]-[Birthdate]. The AgeCalcDate field is a
field containing a date entered manually, which should be
today's date. The second query takes the current age in
days and divides it by 365 to give me the current age in
years, the info. I really want. I tried to include this
years field in the first query, but I could not figure out
how to refer to the current age in days field. When I used
"Expr 2: Expr 1/365," I got an invalid syntax reading, with
the one highlighted. However, "Expr 1/365" works fine
in the second query. I want to use the current age in
years in reports,forms, etc. Well, that's it. Can you see
what I need to do differently? Thanks for you help.

Doug

 

[John Spencer (MVP)]

Try the following function.

Public Function fAge(dtmDOB, Optional dtmDate)
'Returns the Age in years, for dtmDOB.
'Age calculated as of dtmDate, or as of today if dtmDate is missing.

If Not IsDate(dtmDate) Then dtmDate = Date
'If as of date not passed then set to today's date

If IsDate(dtmDOB) Then 'If date passed, then calculate age
fAge = DateDiff("yyyy", dtmDOB, dtmDate) + _
(DateSerial(Year(dtmDate), Month(dtmDOB), Day(dtmDOB)) > dtmDate)
Else
fAge = Null
End If

End Function

I have a query with two fields: date of birth and current
date (entered manually; I tried Now (), but I could not get
an accurate calculation). A third field gives me current
age in days. I then have a second,separate query that
pulls this field current age in days, and converts it then
to age in years. In the first query, the current age in
days results from this expression: Expr 1:
[AgeCalcDate]-[Birthdate]. The AgeCalcDate field is a
field containing a date entered manually, which should be
today's date. The second query takes the current age in
days and divides it by 365 to give me the current age in
years, the info. I really want. I tried to include this
years field in the first query, but I could not figure out
how to refer to the current age in days field. When I used
"Expr 2: Expr 1/365," I got an invalid syntax reading, with
the one highlighted. However, "Expr 1/365" works fine
in the second query. I want to use the current age in
years in reports,forms, etc. Well, that's it. Can you see
what I need to do differently? Thanks for you help.

Doug

I have a function that calculates current age, based on date of birth. I
use this function in a query, to provide a value for a CurrentAge, then use
that CurrentAge field in a report. I am not running into a problem.

How is what you are doing different from my description above?

More info, please...

Jeff Boyce
<Access MVP>

.