Calculating Age

  • Thread starter Thread starter Corinne
  • Start date Start date
C

Corinne

I have a table that has a pupil date of birth, in another table I have tests
taken and the date they were taken. The tables have a one to many
relationship. Is it possible to query the chronological age of the pupil on
the date the test was taken?

Thanks once again for any help you can give
 
-----Original Message-----
I have a table that has a pupil date of birth, in another table I have tests
taken and the date they were taken. The tables have a one to many
relationship. Is it possible to query the chronological age of the pupil on
the date the test was taken?

Thanks once again for any help you can give

you can use datediff to calculate the age difference
Click to expand...
between the date of birth and the date of the test and
then make a query that uses this calculated control. you
may have to format the reusult to get years and months
 
Could you please give me some help on that. I have never done this before,
so I know as much as I would probably put an expression in the query but
don't know what to put.

Thanks
 
Corinne,

Make a calculated field in your query, by putting the equivalent of this
in the Field row of a blank column in the design view of the query...
Age:
DateDiff("yyyy",[DOB],[TestDate)+(Format([DOB],"mmdd")>Format([TestDate],"mmdd"))
.... Obviously substitute your actual field names for the DOB and
TestDate I used in my example.
 
Hi,
Something like this in your query:

Age:DateDiff("yyyy",[yourTestDateField],[yourDOBfield])

take a look at datediff in Help.
 
Oops, I see I left out a ] in my expression! Should be...
Age:
DateDiff("yyyy",[DOB],[TestDate])+(Format([DOB],"mmdd")>Format([TestDate],"mmdd"))
 
oops, DateDiff() will return the number of "boundaries" that are crossed
between dates. For instance:
? datediff("yyyy",#12/31/2003#,#1/1/2004#) = 1
Substituting "m" in the above =1
Substituting "d" in the above =1
Substituting "w" in the above =0 ' the dates are in the same week

--
Duane Hookom
MS Access MVP


Dan Artuso said:
Hi,
Something like this in your query:

Age:DateDiff("yyyy",[yourTestDateField],[yourDOBfield])

take a look at datediff in Help.

--
HTH
Dan Artuso, Access MVP


Could you please give me some help on that. I have never done this before,
so I know as much as I would probably put an expression in the query but
don't know what to put.

Thanks
Click to expand...
Click to expand...
 
Yes, I just read Steve's reply :-)

--
HTH
Dan Artuso, Access MVP


Duane Hookom said:
oops, DateDiff() will return the number of "boundaries" that are crossed
between dates. For instance:
? datediff("yyyy",#12/31/2003#,#1/1/2004#) = 1
Substituting "m" in the above =1
Substituting "d" in the above =1
Substituting "w" in the above =0 ' the dates are in the same week

--
Duane Hookom
MS Access MVP


Dan Artuso said:
Hi,
Something like this in your query:

Age:DateDiff("yyyy",[yourTestDateField],[yourDOBfield])

take a look at datediff in Help.

--
HTH
Dan Artuso, Access MVP


Could you please give me some help on that. I have never done this before,
so I know as much as I would probably put an expression in the query but
don't know what to put.

Thanks


-----Original Message-----
I have a table that has a pupil date of birth, in another
table I have tests
taken and the date they were taken. The tables have a one
to many
relationship. Is it possible to query the chronological
age of the pupil on
the date the test was taken?

Thanks once again for any help you can give

you can use datediff to calculate the age difference
between the date of birth and the date of the test and
then make a query that uses this calculated control. you
may have to format the reusult to get years and months
.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top