Age

[Guest]

Hi i'm trying to create a query that calculates the age of a person. (in many
of the cases the person is dead). I have a date of birth in one field and a
date of death in an other.

I am trying to figure out the age they lived to or the age they are now can
anyone help!

Thanx

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

A usual age formula is this (all one line):

Year(Date())-Year(BirthDate) +
(DateSerial(Year(Date()),Month(BirthDate), Day(BirthDate))>Date())

Age at death would be (all one line):

Year(DeathDate)-Year(BirthDate) +
(DateSerial(Year(DeathDate),Month(BirthDate), Day(BirthDate))>Date())

This relies on the fact that VBA True is -1: The DateSerial comparison
to Date returns 0 or -1.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ9FzboechKqOuFEgEQJHZACg4K5dC9Br4nuKRbq/lfhNv5KJYnoAn1sS
L4sbvCoSuqpsjfR5AK7taOOV
=FZN3
-----END PGP SIGNATURE-----

 

[Guest]

Sorry for maybe sounding stupid but this is my first ever database.
I have a column with DOB and one with DOD. Do I place this formula in the
criteria field?

And what does the VBA bit mean?

 

[John Vinson]

Hi i'm trying to create a query that calculates the age of a person. (in many
of the cases the person is dead). I have a date of birth in one field and a
date of death in an other.

I am trying to figure out the age they lived to or the age they are now can
anyone help!

Thanx

Try typing this expression into a blank Field cell in the query
definition window:

Age: DateDiff("yyyy", [DOB], NZ([DOD], Date()) - IIF(Format(NZ([DOD],
Date()), "mmdd") < Format([DOB], "mmdd"), 1, 0)

This will use the NZ function to return the date of death, or today's
date if that person is still among the living and has a NULL in DOD.

John W. Vinson[MVP]

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

No you put it in the Field cell. E.g.:

Age: Year(Date())-Year(DOB) +
(DateSerial(Year(Date()),Month(DOB), Day(DOB))>Date())

DiedAtAge: Year(DOD)-Year(DOB) +
(DateSerial(Year(DOD),Month(DOD), Day(DOD))>Date())

Don't worry about the VBA thing.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ9GGVoechKqOuFEgEQJslgCeMXpMmFp4COjs+HVB+NGR6ZWgmdYAn0Rr
DOaK2x1astN9skXGKq1IzAdq
=FrAz
-----END PGP SIGNATURE-----