Update Query - Calculation of person's age in Years - DateDiff???

[Guest]

Running Access 2000

I'm setting up a update query to calculate someone's age based upon two
dates and insert that age into a table. So I want to find out how old in
years someone was on particular dates. Example, date of birth on 11/04/1922
and particular date 11/01/2004. So based upon this information the guy is 81
years old. But when I use DateDiff ("yyyy", Date of Birth, Particular date)
the field is filled in with 82 years of age which is wrong. The dates of
birth and the particular date change with each person. What am I missing?

Thanks,

Kelsey

 

[Douglas J. Steele]

You need to take into account whether or not the birth has already occurred:

DateDiff("yyyy", [DOB], Date()) - IIf(Format(Date(), "mmdd") < Format([DOB],
"mmdd"), 1, 0)

 

[Guest]

Doug I don't see where taking into account whether the birth has occured or
not is fixing the problem. Checking to make sure the particular date is
after the DOB doesn't appear to solve the proble. The "DateDiff" as far as I
can see is just taking the differences between the DOB-year and the
particular date-year.

Thank you.

Kelsey

You need to take into account whether or not the birth has already occurred:

DateDiff("yyyy", [DOB], Date()) - IIf(Format(Date(), "mmdd") < Format([DOB],
"mmdd"), 1, 0)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Running Access 2000

I'm setting up a update query to calculate someone's age based upon two
dates and insert that age into a table. So I want to find out how old in
years someone was on particular dates. Example, date of birth on
11/04/1922
and particular date 11/01/2004. So based upon this information the guy is
81
years old. But when I use DateDiff ("yyyy", Date of Birth, Particular
date)
the field is filled in with 82 years of age which is wrong. The dates of
birth and the particular date change with each person. What am I missing?

Thanks,

Kelsey

 

[Adam Turner via AccessMonster.com]

Running Access 2000

I'm setting up a update query to calculate someone's age based upon two
dates and insert that age into a table. So I want to find out how old in
years someone was on particular dates. Example, date of birth on 11/04/1922
and particular date 11/01/2004. So based upon this information the guy is 81
years old. But when I use DateDiff ("yyyy", Date of Birth, Particular date)
the field is filled in with 82 years of age which is wrong. The dates of
birth and the particular date change with each person. What am I missing?

Thanks,

Kelsey

try using:

Age = DateDiff("m", Date of Birth, Particular Date) / 12

 

[Douglas J. Steele]

Typo: I meant "whether the birthday has occurred or not"

Did you try the formula I gave you? I assure you it calculates age
correctly. You're absolutely correct about what DateDiff does: it'll report
that the difference between 2005-12-31 and 2006-01-01 is 1 year. If the
birthday hasn't already happened, you want to deduct 1 from what DateDiff
gives you.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug I don't see where taking into account whether the birth has occured
or
not is fixing the problem. Checking to make sure the particular date is
after the DOB doesn't appear to solve the proble. The "DateDiff" as far
as I
can see is just taking the differences between the DOB-year and the
particular date-year.

Thank you.

Kelsey

You need to take into account whether or not the birth has already
occurred:

DateDiff("yyyy", [DOB], Date()) - IIf(Format(Date(), "mmdd") <
Format([DOB],
"mmdd"), 1, 0)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Running Access 2000

I'm setting up a update query to calculate someone's age based upon two
dates and insert that age into a table. So I want to find out how old
in
years someone was on particular dates. Example, date of birth on
11/04/1922
and particular date 11/01/2004. So based upon this information the guy
is
81
years old. But when I use DateDiff ("yyyy", Date of Birth, Particular
date)
the field is filled in with 82 years of age which is wrong. The dates
of
birth and the particular date change with each person. What am I
missing?

Thanks,

Kelsey

 

[Adam Turner via AccessMonster.com]

Running Access 2000

I'm setting up a update query to calculate someone's age based upon two
dates and insert that age into a table. So I want to find out how old in
years someone was on particular dates. Example, date of birth on 11/04/1922
and particular date 11/01/2004. So based upon this information the guy is 81
years old. But when I use DateDiff ("yyyy", Date of Birth, Particular date)
the field is filled in with 82 years of age which is wrong. The dates of
birth and the particular date change with each person. What am I missing?

Thanks,

Kelsey

Kelsey,

The problem is that in years, the difference is 82 years. Even using a month
comparison, it would show a wrong calculation on the month they were born
because they are that age on that entire month. The most accurate approach
would have to be in days, but there are a few checks you need as follows:

If Month(Birthday) = Month(Particular date) Then
If Day(Birthday) < Day(Particular date) Then
Age = Int(DateDiff("d", Birthday, Particular date) / 365)
Else
Age = Int(DateDiff("d", Birthday, Particular date) / 356 - 1)
End If
Else
Age = Int(DateDiff("d", Birthday, Particular date) / 365)
End If

 

[Adam Turner via AccessMonster.com]

Running Access 2000

[quoted text clipped - 9 lines]

Kelsey

Kelsey,

The problem is that in years, the difference is 82 years. Even using a month
comparison, it would show a wrong calculation on the month they were born
because they are that age on that entire month. The most accurate approach
would have to be in days, but there are a few checks you need as follows:

If Month(Birthday) = Month(Particular date) Then
If Day(Birthday) < Day(Particular date) Then
Age = Int(DateDiff("d", Birthday, Particular date) / 365)
Else
Age = Int(DateDiff("d", Birthday, Particular date) / 356 - 1)
End If
Else
Age = Int(DateDiff("d", Birthday, Particular date) / 365)
End If

ooops...that (356 - 1) should be 365 - 1...sorry

 

[Rick Brandt]

Running Access 2000

[quoted text clipped - 9 lines]

Kelsey

Kelsey,

The problem is that in years, the difference is 82 years. Even using
a month comparison, it would show a wrong calculation on the month
they were born because they are that age on that entire month. The
most accurate approach would have to be in days, but there are a few
checks you need as follows:

If Month(Birthday) = Month(Particular date) Then
If Day(Birthday) < Day(Particular date) Then
Age = Int(DateDiff("d", Birthday, Particular date) / 365)
Else
Age = Int(DateDiff("d", Birthday, Particular date) / 356 - 1)
End If
Else
Age = Int(DateDiff("d", Birthday, Particular date) / 365)
End If

ooops...that (356 - 1) should be 365 - 1...sorry

Variations on the solution posted by Douglas Steele work perfectly even in
leap years and are posted frequently as an answer to this question. In case
you missed his post...

DateDiff("yyyy", [DOB], Date()) - IIf(Format(Date(), "mmdd") < Format([DOB],
"mmdd"), 1, 0)

 

[Adam Turner via AccessMonster.com]

[quoted text clipped - 21 lines]

ooops...that (356 - 1) should be 365 - 1...sorry

Variations on the solution posted by Douglas Steele work perfectly even in
leap years and are posted frequently as an answer to this question. In case
you missed his post...

DateDiff("yyyy", [DOB], Date()) - IIf(Format(Date(), "mmdd") < Format([DOB],
"mmdd"), 1, 0)

Yes this works fine: MsgBox DateDiff("yyyy", "10/28/1974", "10/25/05") - IIf
(Format("10/24/05", "mmdd") < Format("10/28/1974", "mmdd"), 1, 0)

I think the problem was the verbiage.

 

[Adam Turner via AccessMonster.com]

[quoted text clipped - 21 lines]

ooops...that (356 - 1) should be 365 - 1...sorry

Variations on the solution posted by Douglas Steele work perfectly even in
leap years and are posted frequently as an answer to this question. In case
you missed his post...

DateDiff("yyyy", [DOB], Date()) - IIf(Format(Date(), "mmdd") < Format([DOB],
"mmdd"), 1, 0)

....and very elegant