Calculate "Age" useing a "Birthdate" field (IFF?)

[Jesse]

Hi -

I'm using a Form with the field "Birthdate" (Medium date).

I also have a field "Age" that I want to be calculated after I enter in the
Birthdate.
But I want the "Age" at Sept 30 2005.

So if the Month is Sept or earlier, the expression should be "2005-YEAR".

But if the Month is Oct-Nov-Dec, the experssion should be "2005-YEAR-1".

How do I do this?
Any suggestions?

Thanks!
Jesse

 

[Mike Painter]

Hi -

I'm using a Form with the field "Birthdate" (Medium date).

I also have a field "Age" that I want to be calculated after I enter
in the Birthdate.
But I want the "Age" at Sept 30 2005.

So if the Month is Sept or earlier, the expression should be
"2005-YEAR".

But if the Month is Oct-Nov-Dec, the experssion should be
"2005-YEAR-1".

How do I do this?
Any suggestions?

Thanks!
Jesse

Originally posted by Ken Getz:

Function GetAge(dtmBD as Date) As Integer
GetAge = DateDiff("yyyy", dtmBD, Date) + _
(Date < DateSerial(Year(Date), Month(dtmBD), Day(dtmBD)))
End Function

Basically, this counts on the fact that a True expression has a value of
-1, and so if the current date is less than the birthdate value in the
current year, it subtracts one from the year difference between the two.

If you're using Access 95 or 97, use a Date variable rather than a
variant.

--Ken

GetAge = DateDiff("yyyy", dtmBD, Date) +(Date < DateSerial(Year(Date),
Month(dtmBD),Day(dtmBD)))

 

[Rick B]

To calculate the age as of Sept 30, 2005 I would think you could take the
traditional age calculation and simply replace the current date with Sep 30,
2005.

Calculate age...

DateDiff("yyyy",[Birthdate],Date())+(Format([Birthdate],"mmdd")>Format(Date(
),"mmdd"))





Calculate age as of 9/30/2005...


DateDiff("yyyy",[Birthdate],#09/30/2005#)+(Format([Birthdate],"mmdd")>Format
(#09/30/2005#,"mmdd"))





Hope that helps,

MERRY CHRISTMAS!!!

 

[Jesse]

Hi,

This is what I ended up using...

=Int(DateDiff('d',[Date of Birth],#30/09/2005#)/365.25)

....it works perfectly!

As I understand it, the function calculates the difference in days between
[Date of Birth] and Sept 30, 2005 [30/09/2005] and divides this number by
365.25 (to accomodate leap year). This yields decimal values, so the integer
INT() function is used to display the person's age.

For example, someone born on November 1, 2005 would be 21.91 years old as of
Sept 30, 2005 - but their age is shown as 21.

-Jesse

To calculate the age as of Sept 30, 2005 I would think you could take the
traditional age calculation and simply replace the current date with Sep 30,
2005.

Calculate age...

DateDiff("yyyy",[Birthdate],Date())+(Format([Birthdate],"mmdd")>Format(Date(
),"mmdd"))





Calculate age as of 9/30/2005...


DateDiff("yyyy",[Birthdate],#09/30/2005#)+(Format([Birthdate],"mmdd")>Format
(#09/30/2005#,"mmdd"))





Hope that helps,

MERRY CHRISTMAS!!!

Hi -

I'm using a Form with the field "Birthdate" (Medium date).

I also have a field "Age" that I want to be calculated after I enter in the
Birthdate.
But I want the "Age" at Sept 30 2005.

So if the Month is Sept or earlier, the expression should be "2005-YEAR".

But if the Month is Oct-Nov-Dec, the experssion should be "2005-YEAR-1".

How do I do this?
Any suggestions?

Thanks!
Jesse

 

[Douglas J. Steele]

Remember that there aren't exactly 365.25 days in a year. However, the
inaccuracy that approximation will introduce probably won't cause many
problems.

The formula Rick B provided is considered far superior, though. It
calculates the number of years between the Birthdate and the date in
question. Because the DateDiff function is a lilttle too literal, and treats
the difference between 31 Dec, 2004 and 1 Jan, 2005 as a year, you need to
adjust the value it returns if the birthday hasn't yet occurred in the test
year (2005, in this case). That's what the
(Format([Birthdate],"mmdd")>Format(Date(),"mmdd")) part is for. That formats
the month and day of birth and compares it to the current month and day. If
the formatted birthdate is greater than the formatted date (i.e.: the
birthday hasn't yet occurred), the expression returns True, or -1, so that
the DateDiff calculation is reduced by one.

BTW, the only reason #30/09/2005# works in your example is because there is
no 30th month. If you were to use #1/10/2005#, Access will treat it as 10
Jan, 2005, regardless of what your regional settings are.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi,

This is what I ended up using...

=Int(DateDiff('d',[Date of Birth],#30/09/2005#)/365.25)

...it works perfectly!

As I understand it, the function calculates the difference in days between
[Date of Birth] and Sept 30, 2005 [30/09/2005] and divides this number by
365.25 (to accomodate leap year). This yields decimal values, so the
integer
INT() function is used to display the person's age.

For example, someone born on November 1, 2005 would be 21.91 years old as
of
Sept 30, 2005 - but their age is shown as 21.

-Jesse

To calculate the age as of Sept 30, 2005 I would think you could take the
traditional age calculation and simply replace the current date with Sep
30,
2005.

Calculate age...

DateDiff("yyyy",[Birthdate],Date())+(Format([Birthdate],"mmdd")>Format(Date(
),"mmdd"))





Calculate age as of 9/30/2005...


DateDiff("yyyy",[Birthdate],#09/30/2005#)+(Format([Birthdate],"mmdd")>Format
(#09/30/2005#,"mmdd"))





Hope that helps,

MERRY CHRISTMAS!!!

Hi -

I'm using a Form with the field "Birthdate" (Medium date).

I also have a field "Age" that I want to be calculated after I enter in the
Birthdate.
But I want the "Age" at Sept 30 2005.

So if the Month is Sept or earlier, the expression should be
"2005-YEAR".

But if the Month is Oct-Nov-Dec, the experssion should be
"2005-YEAR-1".

How do I do this?
Any suggestions?

Thanks!
Jesse

 

[Dirk Goldgar]

Hi,

This is what I ended up using...

=Int(DateDiff('d',[Date of Birth],#30/09/2005#)/365.25)

...it works perfectly!

As I understand it, the function calculates the difference in days
between [Date of Birth] and Sept 30, 2005 [30/09/2005] and divides
this number by 365.25 (to accomodate leap year). This yields decimal
values, so the integer INT() function is used to display the person's
age.

For example, someone born on November 1, 2005 would be 21.91 years
old as of Sept 30, 2005 - but their age is shown as 21.

You should be aware that your formula is not in fact perfect, because
leap years don't occur every 4 years. The formua Rick B. posted doesn't
have this flaw.

 

[Jesse]

Ok ok...

But when I put in Rick's expression it doesnt work. All I get is: #Name?

Here is what I have entered:

=DateDiff("yyyy",[Date of Birth],#09/30/2005#)+(Format([Date of
Birth],"mmdd")>[Format](#09/30/2005#,"mmdd"))

Any suggestions on how to solve this?

THANKS!
Jesse

Remember that there aren't exactly 365.25 days in a year. However, the
inaccuracy that approximation will introduce probably won't cause many
problems.

The formula Rick B provided is considered far superior, though. It
calculates the number of years between the Birthdate and the date in
question. Because the DateDiff function is a lilttle too literal, and treats
the difference between 31 Dec, 2004 and 1 Jan, 2005 as a year, you need to
adjust the value it returns if the birthday hasn't yet occurred in the test
year (2005, in this case). That's what the
(Format([Birthdate],"mmdd")>Format(Date(),"mmdd")) part is for. That formats
the month and day of birth and compares it to the current month and day. If
the formatted birthdate is greater than the formatted date (i.e.: the
birthday hasn't yet occurred), the expression returns True, or -1, so that
the DateDiff calculation is reduced by one.

BTW, the only reason #30/09/2005# works in your example is because there is
no 30th month. If you were to use #1/10/2005#, Access will treat it as 10
Jan, 2005, regardless of what your regional settings are.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi,

This is what I ended up using...

=Int(DateDiff('d',[Date of Birth],#30/09/2005#)/365.25)

...it works perfectly!

As I understand it, the function calculates the difference in days between
[Date of Birth] and Sept 30, 2005 [30/09/2005] and divides this number by
365.25 (to accomodate leap year). This yields decimal values, so the
integer
INT() function is used to display the person's age.

For example, someone born on November 1, 2005 would be 21.91 years old as
of
Sept 30, 2005 - but their age is shown as 21.

-Jesse

To calculate the age as of Sept 30, 2005 I would think you could take the
traditional age calculation and simply replace the current date with Sep
30,
2005.

Calculate age...

DateDiff("yyyy",[Birthdate],Date())+(Format([Birthdate],"mmdd")>Format(Date(
),"mmdd"))





Calculate age as of 9/30/2005...


DateDiff("yyyy",[Birthdate],#09/30/2005#)+(Format([Birthdate],"mmdd")>Format
(#09/30/2005#,"mmdd"))





Hope that helps,

MERRY CHRISTMAS!!!



Hi -

I'm using a Form with the field "Birthdate" (Medium date).

I also have a field "Age" that I want to be calculated after I enter in
the
Birthdate.
But I want the "Age" at Sept 30 2005.

So if the Month is Sept or earlier, the expression should be
"2005-YEAR".

But if the Month is Oct-Nov-Dec, the experssion should be
"2005-YEAR-1".

How do I do this?
Any suggestions?

Thanks!
Jesse

 

[Dirk Goldgar]

Ok ok...

But when I put in Rick's expression it doesnt work. All I get is:
#Name?

Here is what I have entered:

=DateDiff("yyyy",[Date of Birth],#09/30/2005#)+(Format([Date of
Birth],"mmdd")>[Format](#09/30/2005#,"mmdd"))

That's not what Rick posted. Where did the square brackets around
"Format" come from? Try:

=DateDiff("yyyy",[Date of Birth],#09/30/2005#)+
(Format([Date of Birth],"mmdd")>Format(#09/30/2005#,"mmdd"))

That really should all be on one line, but I had to break it onto two
lines to post it.

 

[Jesse]

I found this formula from another post on this board (Dave Bradshaw
11/25/2004):

=IIf(DateAdd("yyyy",DateDiff("yyyy",[Date of Birth],#10/01/2005#),[Date of
Birth])<#10/01/2005#,0,-1)+DateDiff("yyyy",[Date of Birth],#10/01/2005#)

I changed it to calculate as of Oct 1 instead of Sep 30 because I want
people born on Sep 29 and Sep 30 to have the same age.

This one seems to work very nicely - do you forsee any problems?

Thanks again,
Jesse

Ok ok...

But when I put in Rick's expression it doesnt work. All I get is: #Name?

Here is what I have entered:

=DateDiff("yyyy",[Date of Birth],#09/30/2005#)+(Format([Date of
Birth],"mmdd")>[Format](#09/30/2005#,"mmdd"))

Any suggestions on how to solve this?

THANKS!
Jesse

Remember that there aren't exactly 365.25 days in a year. However, the
inaccuracy that approximation will introduce probably won't cause many
problems.

The formula Rick B provided is considered far superior, though. It
calculates the number of years between the Birthdate and the date in
question. Because the DateDiff function is a lilttle too literal, and treats
the difference between 31 Dec, 2004 and 1 Jan, 2005 as a year, you need to
adjust the value it returns if the birthday hasn't yet occurred in the test
year (2005, in this case). That's what the
(Format([Birthdate],"mmdd")>Format(Date(),"mmdd")) part is for. That formats
the month and day of birth and compares it to the current month and day. If
the formatted birthdate is greater than the formatted date (i.e.: the
birthday hasn't yet occurred), the expression returns True, or -1, so that
the DateDiff calculation is reduced by one.

BTW, the only reason #30/09/2005# works in your example is because there is
no 30th month. If you were to use #1/10/2005#, Access will treat it as 10
Jan, 2005, regardless of what your regional settings are.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi,

This is what I ended up using...

=Int(DateDiff('d',[Date of Birth],#30/09/2005#)/365.25)

...it works perfectly!

As I understand it, the function calculates the difference in days between
[Date of Birth] and Sept 30, 2005 [30/09/2005] and divides this number by
365.25 (to accomodate leap year). This yields decimal values, so the
integer
INT() function is used to display the person's age.

For example, someone born on November 1, 2005 would be 21.91 years old as
of
Sept 30, 2005 - but their age is shown as 21.

-Jesse


:

To calculate the age as of Sept 30, 2005 I would think you could take the
traditional age calculation and simply replace the current date with Sep
30,
2005.

Calculate age...

DateDiff("yyyy",[Birthdate],Date())+(Format([Birthdate],"mmdd")>Format(Date(
),"mmdd"))





Calculate age as of 9/30/2005...


DateDiff("yyyy",[Birthdate],#09/30/2005#)+(Format([Birthdate],"mmdd")>Format
(#09/30/2005#,"mmdd"))





Hope that helps,

MERRY CHRISTMAS!!!



Hi -

I'm using a Form with the field "Birthdate" (Medium date).

I also have a field "Age" that I want to be calculated after I enter in
the
Birthdate.
But I want the "Age" at Sept 30 2005.

So if the Month is Sept or earlier, the expression should be
"2005-YEAR".

But if the Month is Oct-Nov-Dec, the experssion should be
"2005-YEAR-1".

How do I do this?
Any suggestions?

Thanks!
Jesse

 

[Mike Painter]

Hi,

This is what I ended up using...

=Int(DateDiff('d',[Date of Birth],#30/09/2005#)/365.25)

...it works perfectly!

This works in the majority of cases but you will find errors when the DOB is
near the current date and you want to find the age.
These errors vary with the actual birthdate and the year WRT to a leap year.

 

[Jesse]

THANKS!!!!

Ok ok...

But when I put in Rick's expression it doesnt work. All I get is:
#Name?

Here is what I have entered:

=DateDiff("yyyy",[Date of Birth],#09/30/2005#)+(Format([Date of
Birth],"mmdd")>[Format](#09/30/2005#,"mmdd"))

That's not what Rick posted. Where did the square brackets around
"Format" come from? Try:

=DateDiff("yyyy",[Date of Birth],#09/30/2005#)+
(Format([Date of Birth],"mmdd")>Format(#09/30/2005#,"mmdd"))

That really should all be on one line, but I had to break it onto two
lines to post it.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Douglas J. Steele]

The first part is a rather convoluted way of figuring out whether or not the
birthday's already occurred, but other than that, it should work.

I think

=IIf(DateSerial(2005, Month([Date of Birth]), Day([Date of Birth]) <
#10/01/2005#, 0, -1) + DateDiff("yyyy", [Date of Birth], #10/01/2005#)

is simpler to understand, although I don't understand why you needed
something different than what you were already given.

To ensure that people whose birthday is on the date you're checking, use <=
instead of <.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

I found this formula from another post on this board (Dave Bradshaw
11/25/2004):

=IIf(DateAdd("yyyy",DateDiff("yyyy",[Date of Birth],#10/01/2005#),[Date of
Birth])<#10/01/2005#,0,-1)+DateDiff("yyyy",[Date of Birth],#10/01/2005#)

I changed it to calculate as of Oct 1 instead of Sep 30 because I want
people born on Sep 29 and Sep 30 to have the same age.

This one seems to work very nicely - do you forsee any problems?

Thanks again,
Jesse

Ok ok...

But when I put in Rick's expression it doesnt work. All I get is: #Name?

Here is what I have entered:

=DateDiff("yyyy",[Date of Birth],#09/30/2005#)+(Format([Date of
Birth],"mmdd")>[Format](#09/30/2005#,"mmdd"))

Any suggestions on how to solve this?

THANKS!
Jesse

Remember that there aren't exactly 365.25 days in a year. However, the
inaccuracy that approximation will introduce probably won't cause many
problems.

The formula Rick B provided is considered far superior, though. It
calculates the number of years between the Birthdate and the date in
question. Because the DateDiff function is a lilttle too literal, and treats
the difference between 31 Dec, 2004 and 1 Jan, 2005 as a year, you need to
adjust the value it returns if the birthday hasn't yet occurred in the test
year (2005, in this case). That's what the
(Format([Birthdate],"mmdd")>Format(Date(),"mmdd")) part is for. That formats
the month and day of birth and compares it to the current month and day. If
the formatted birthdate is greater than the formatted date (i.e.: the
birthday hasn't yet occurred), the expression returns True, or -1, so that
the DateDiff calculation is reduced by one.

BTW, the only reason #30/09/2005# works in your example is because there is
no 30th month. If you were to use #1/10/2005#, Access will treat it as 10
Jan, 2005, regardless of what your regional settings are.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Hi,

This is what I ended up using...

=Int(DateDiff('d',[Date of Birth],#30/09/2005#)/365.25)

...it works perfectly!

As I understand it, the function calculates the difference in days between
[Date of Birth] and Sept 30, 2005 [30/09/2005] and divides this number by
365.25 (to accomodate leap year). This yields decimal values, so the
integer
INT() function is used to display the person's age.

For example, someone born on November 1, 2005 would be 21.91 years old as
of
Sept 30, 2005 - but their age is shown as 21.

-Jesse


:

To calculate the age as of Sept 30, 2005 I would think you could take the
traditional age calculation and simply replace the current date with Sep
30,
2005.

Calculate age...

DateDiff("yyyy",[Birthdate],Date())+(Format([Birthdate],"mmdd")>Format(Date(
),"mmdd"))





Calculate age as of 9/30/2005...


DateDiff("yyyy",[Birthdate],#09/30/2005#)+(Format([Birthdate],"mmdd")>Format
(#09/30/2005#,"mmdd"))





Hope that helps,

MERRY CHRISTMAS!!!



Hi -

I'm using a Form with the field "Birthdate" (Medium date).

I also have a field "Age" that I want to be calculated after I enter in
the
Birthdate.
But I want the "Age" at Sept 30 2005.

So if the Month is Sept or earlier, the expression should be
"2005-YEAR".

But if the Month is Oct-Nov-Dec, the experssion should be
"2005-YEAR-1".

How do I do this?
Any suggestions?

Thanks!
Jesse