DateDiff function to calculate age

[Ray]

Hi,

When using DateDiff to determine age, I am getting
negative age results for anyone born before 1930. Also,
the absolute age value for these people do not actually
correspond to their age, but instead appears to be the
difference between the last 2 digits in their birth year
and the last 2 digits in year 2004 (eg. a person born in
1920 returns an age of -16 in my query). All dates after
1930 appear to be correct. What am I doing wrong?

Ray

 

[[MVP] S.Clark]

? datediff("yyyy",#1/1/1929#,#9/15/2004#) = 75 for me.

What are you doing different from this?

 

[Ray]

Hi Steve,

I am using the following equation, with dob being the date
of birth in my table: AGE: DateDiff("yyyy",[dob],Now())

Ray

 

[fredg]

Hi Steve,

I am using the following equation, with dob being the date
of birth in my table: AGE: DateDiff("yyyy",[dob],Now())

Ray

-----Original Message-----
? datediff("yyyy",#1/1/1929#,#9/15/2004#) = 75 for me.

What are you doing different from this?

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting




.

Ray,
1) Your expression > AGE: DateDiff("yyyy",[dob],Now()) < won't work.

Substituting real values...
AGE: DateDiff("yyyy",#12/31/2003#,#1/1/2004#) will return 1 year,
which I don't believe is the actual age expected.

Here is a function that will accurately compute age whether or not the
month and day the query is run is before, on, or after the month and
day of birth.

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

2) Further, you are storing years as 2 digit years. Since Y2K, Access
assumes any 2 digit year from 00 thru 29 as 2000 thru 2029.
Any 2 digit year from 30 thru 99 as 1930 thru 1999.
Update your data and computer to correctly store 4 digit years, and
the negative values will go away.

 

[Guest]

I did not realize Access stored the dates in that manner.
I have changed all my date fields to reflect a 4 digit
year and this seems to have cleared my problem. Thank you.

Ray

-----Original Message-----

Hi Steve,

I am using the following equation, with dob being the date
of birth in my table: AGE: DateDiff("yyyy",[dob],Now())

Ray

-----Original Message-----
? datediff("yyyy",#1/1/1929#,#9/15/2004#) = 75 for me.

What are you doing different from this?

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

Hi,

When using DateDiff to determine age, I am getting
negative age results for anyone born before 1930. Also,
the absolute age value for these people do not actually
correspond to their age, but instead appears to be the
difference between the last 2 digits in their birth year
and the last 2 digits in year 2004 (eg. a person born in
1920 returns an age of -16 in my query). All dates after
1930 appear to be correct. What am I doing wrong?

Ray


.

Ray,
1) Your expression > AGE: DateDiff("yyyy",[dob],Now()) < won't work.

Substituting real values...
AGE: DateDiff("yyyy",#12/31/2003#,#1/1/2004#) will return 1 year,
which I don't believe is the actual age expected.

Here is a function that will accurately compute age whether or not the
month and day the query is run is before, on, or after the month and
day of birth.

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

2) Further, you are storing years as 2 digit years. Since Y2K, Access
assumes any 2 digit year from 00 thru 29 as 2000 thru 2029.
Any 2 digit year from 30 thru 99 as 1930 thru 1999.
Update your data and computer to correctly store 4 digit years, and
the negative values will go away.

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.