Age Calculation

[Guest]

I succesfully run a query calculating members age from a birthdate field. The
funny thing is that the query won't calculate properly people who was born
before 1930.
E.g. if someone was born 1929 it gives me a -24 result, which in reality
should be 76 years of age. Any idea of what is going on?
Ah just for clarification the birthdate field is the following format:
07-feb-29
Thanks in advance.

 

[Michel Walsh]

Hi,



It seems you have a problem with default century and you use only two digits
for the year, rather than four digits, ex, 05 instead of 2005.

A possible solution is to add 100 and take the modulo 100:


( 100 + yourActualExpression ) MOD 100



.... and that assumes no-one has over 99 years of age... bad thing but
unlikely manageable if you persist in using only two digits for the year.
As example, what would happen with someone born in 1904, and still living?
you can't tell that it is 1904 rather than 2004 in the first place!


Hoping it may help,
Vanderghast, Access MVP

 

[John Spencer]

Do the letters Y2K signify anything to you?

Access is interpreting the date as being 2029 not 1929. What type of field
is the date being stored in? If it is a text field, then you will have to
adjust the data. Access interprets 2 character years as 1900 if the value
is greater than 29 and as 2000 if the value is less than 30.

You may be able to "fix" the problem by using the following to "correct" the
date

IIF(Year(DateValue(YourDateField))>2000,DateAdd("yyyy",-1000,DateValue(YourDateField)),DateValue(YourDateField))

 

[Guest]

Hi Thanks for your help... when you say possible solution :
(100+ your actual expression) MOD 100
Question: where/how do I insert this expression? Is it only 100+ then my
expression? does it need parenthesis and what about MOD 100 does this go
anywhere?

 

[Guest]

Hi, thanks for your help.
Y"K means nothing to me? Anything I should know?
As to fix the problem, where do I place the expression you suggest?
The field is in a text field with a date format mask to provide
standarisation. and the field is part of a table.

 

[John Spencer]

Y2K (The Year 2000) was a potential major problem in the computer industry.
The problem was that many databases and other applications stored dates with
only 2 characters for the year. What that meant was that suddenly (as of
January 1, 2000) dates would be causing lots of problems when trying to
sort, compare etc.

You can use the formula (complex as it is) where you would normally use the
date of birth that you are storing as a text string.

The formula converts your date string into a DateTime type of variable.

Another method might be to use Michel Walsh's method.

The best thing you could do would be to change the field type to a DateTime
field and then find all the records with DOB greater than the current date
and fix them. This could be a bit of work, since you may have to fix other
parts of your database that relied on the field being a string field. In
the long run, you will probably be a lot better off storing the DOB in a
datetime field.