Need Help With Determining Age

[bhipwell via AccessMonster.com]

I need to determine a perons age dependent upon whether or not the
individual's birthday MONTH has / will occur prior to a predetermined date
this year. Some examples:

Example 1:
Birthday: 1/15/79
Specified Date: 3/1/2009
Answer = Person is 29

Example 2:
Birthday: 1/15/79
Specificed Date: 1/1/2009
Answer = Person is 30

Example 3:
Birthday: 2/15/79
Specified Date: 2/1/09
Answer = Person is 29 (calculation can't make him 30 until the month has
ended)

Please help. This code will be in a query, non SQL formula is easiest for me.
(FYI, this is used to help calculate costs for various age brackets, the
company only changes the rates for the persons age once a year, the specified
date)

Thanks!

BH

 

[fredg]

I need to determine a perons age dependent upon whether or not the
individual's birthday MONTH has / will occur prior to a predetermined date
this year. Some examples:

Example 1:
Birthday: 1/15/79
Specified Date: 3/1/2009
Answer = Person is 29

Example 2:
Birthday: 1/15/79
Specificed Date: 1/1/2009
Answer = Person is 30

Example 3:
Birthday: 2/15/79
Specified Date: 2/1/09
Answer = Person is 29 (calculation can't make him 30 until the month has
ended)

Please help. This code will be in a query, non SQL formula is easiest for me.
(FYI, this is used to help calculate costs for various age brackets, the
company only changes the rates for the persons age once a year, the specified
date)

Thanks!

BH

To calculate a person's age as of the current date:

In a query:
Age: DateDiff("yyyy",[DOB],Date())-IIf(Format([DOB],
"mmdd")>Format(Date(),"mmdd"),1,0)

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

Where [DOB] is the birthdate field.

In a query, to calculate a persons age as of a user's [Specified Date]
change the 2 instances of Date() to [Specified Date].
The user will be prompted to enter a valid date.

On a form or Report, to calculate a persons age as of a user's
"Specified Date" change the 2 instances of Date() to [Name of Control
that the user has entered the date in].

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.

 

[bhipwell via AccessMonster.com]

With your help, I was able to get exactly the result I was looking for.

Thanks!

BH

(Man I love this site)