Need help on formula for age date of birth access

[Kaz Holder]

I need help on the formula given to change control source for calculating age
from date of birth on forms in access

 

[JL]

Hi Kaz,

This should calculate the age based on birthday to present.

DateDiff("yyyy",#DateOfBirth#,Now())

Hope this helps.

 

[fredg]

I need help on the formula given to change control source for calculating age
from date of birth on forms in access

Directly as the control source of an unbound control:
=DateDiff("yyyy",[DOB],Date())-IIf(Format([DOB],"mmdd")>Format(Date(),
"mmdd"),1,0)

You do know, I hope, that this Age computation should NOT be stored in
any table.
Just compute it and display it on a form or report, as needed.

 

[John Vinson]

Hi Kaz,

This should calculate the age based on birthday to present.

DateDiff("yyyy",#DateOfBirth#,Now())

Actually this will calculate the number of January 1sts that have
passed between the date of birth and now, and because of the invalid #
syntax it won't work anyway!

Try it: if the field DateOfBirth contains #12/25/2003# and you run the
query on January 6, it will say that the 12-day old baby is one year
old.

Instead, use

DateDiff("yyyy", [DateOfBirth], Date()) - IIF(Format([DateOfBirth],
"mmdd") > Format(Date(), "mmdd"), 1, 0)

to subtract a year if the birthday anniversary has not yet arrived.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[JL]

Hi John,

Thanks, never really have to do age calc. Just know DateDiff will give
difference between two dates. Learn something new everyday.

Hi Kaz,

This should calculate the age based on birthday to present.

DateDiff("yyyy",#DateOfBirth#,Now())

Actually this will calculate the number of January 1sts that have
passed between the date of birth and now, and because of the invalid #
syntax it won't work anyway!

Try it: if the field DateOfBirth contains #12/25/2003# and you run the
query on January 6, it will say that the 12-day old baby is one year
old.

Instead, use

DateDiff("yyyy", [DateOfBirth], Date()) - IIF(Format([DateOfBirth],
"mmdd") > Format(Date(), "mmdd"), 1, 0)

to subtract a year if the birthday anniversary has not yet arrived.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps