Calculate age

  • Thread starter Thread starter Denis
  • Start date Start date
D

Denis

Is it possible to create an expression to calculate
current age from a medium date?

Thanks, Denis.
 
Is it possible to create an expression to calculate
current age from a medium date?

Thanks, Denis.
Click to expand...

From *any* date (the format is completely irrelevant):

Age: DateDiff("yyyy", [birthdate], Date()) - IIF(Format([birthdate],
"mmdd") > Format(Date(), "mmdd"), 1, 0)
 
This seems to work for ages up to 74, after that I get
negative values, e.g. for birthdate "15-May-29", I get
age "-25". What am I doing wrong?

Denis
-----Original Message-----
Is it possible to create an expression to calculate
current age from a medium date?

Thanks, Denis.
Click to expand...

From *any* date (the format is completely irrelevant):

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



.
Click to expand...
 
That's one of the problems with only having a 2 digit year. You're unaware
of the fact that 15-May-29 has been stored in your database as 15-May-2029!

If you go to Regional Settings in the control panel, you'll see that there's
an option that indicates "When a two-digit year is entered, interpret it as
a year between:". (How you find this setting depends on your operating
system. In XP, you need to click on the "Customize" button on the Regional
Options tab, and it'll be on the Date tab of the new form that appears. In
older OS, I believe the Date tab is there from the start). The default is
between 1930 and 2029: in other words, 3 will be interpretted as 2003, 29
will be interpretted as 2029, and 30 will be interpretted as 1930.

You're going to have to go back and correct how your dates are being input
to ensure that they're captured correctly, plus you'll need to write an
Update query to adjust all of the dates that have been captured incorrectly
already.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Denis said:
This seems to work for ages up to 74, after that I get
negative values, e.g. for birthdate "15-May-29", I get
age "-25". What am I doing wrong?

Denis
-----Original Message-----
Is it possible to create an expression to calculate
current age from a medium date?

Thanks, Denis.
Click to expand...

From *any* date (the format is completely irrelevant):

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



.
Click to expand...
Click to expand...
 
This seems to work for ages up to 74, after that I get
negative values, e.g. for birthdate "15-May-29", I get
age "-25". What am I doing wrong?
Click to expand...

The same thing innumerable programmers did during the 20th century,
leading to the "Y2K Problem" of recent fame.

My friend Lida was born in '97. I believe she has a
great-great-granddaughter also born in '97.

Birthdates MUST be displayed with four digit years, otherwise Access
will assume that dates from 00 through 29 are in the 21st century, and
30 through 99 in the 20th (since the program must make SOME reasonable
assumption to resolve the ambiguity).
 
Back
Top