Need help on formula for age date of birth access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

This should calculate the age based on birthday to present.

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

Hope this helps.
 
I need help on the formula given to change control source for calculating age
from date of birth on forms in access
Click to expand...


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.
 
Hi Kaz,

This should calculate the age based on birthday to present.

DateDiff("yyyy",#DateOfBirth#,Now())
Click to expand...

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
 
Hi John,

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


John Vinson said:
Hi Kaz,

This should calculate the age based on birthday to present.

DateDiff("yyyy",#DateOfBirth#,Now())
Click to expand...

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
Click to expand...
 
Back
Top