Negative values for age calculation

[Guest]

I use the following to calculate age from birthdate and today's date:
"=DateDiff("yyyy",[Birthdate],Now())+Int(Format(Now(),"mmdd")<Format([Birthdate],"mmdd"))".

Often for older birthdates the calculation yields a negative value, for
birthdate in 1918, age comes back as "-13".

What am I doing wrong?

 

[Ken Snell \(MVP\)]

If you don't specify four-year dates as the option for the format, ACCESS
uses the built-in assumptions for two-digit years. From Help file:

a.. This feature has a 100-year span. The default span is 1930 to 2029. A
program that uses the default option to interpret two-digit years will
consider the years between (and including) 30 and 99 to be preceded by 19,
and years between (and including) 00 and 29 to be preceded by 20. For
example, 99 will be interpreted as 1999 and 01 will be interpreted as 2001.

So this may work better for you:

"=DateDiff("yyyy",DateSerial(Year([Birthdate]),Month([Birthdate]),Day([Birthdate])),
Now())+Int(Format(Now(),"mmdd")<Format(DateSerial(Year([Birthdate]),Month([Birthdate]),Day([Birthdate])),"mmdd"))"

 

[tina]

when you enter a date before 1930 in a date field, if you do not explicitly
type in the entire year - as 1929, 1918, etc - then what the system saves is
2029, 2018, etc. if you've formatted the field to *display* mmddyy, you'll
never know the difference - until you use the date in a calculation.

hth

 

[Ken Snell \(MVP\)]

Thanks, tina..Your explanation is more cogent than mine... < g >

 

[tina]

well, that's gotta be a first - that i was more cogent, or at least more
succinct, than just about anybody in the NGs. usually i'm the living
embodiment of "never use one word where ten will do" and "never go straight
from a to b to c, when you can take the scenic route"! <bg>

 

[James A. Fortune]

I use the following to calculate age from birthdate and today's date:
"=DateDiff("yyyy",[Birthdate],Now())+Int(Format(Now(),"mmdd")<Format([Birthdate],"mmdd"))".

Often for older birthdates the calculation yields a negative value, for
birthdate in 1918, age comes back as "-13".

What am I doing wrong?

I don't think the formula I use has that problem:

http://groups.google.com/group/microsoft.public.access/msg/5a1626d8b2777c23

James A. Fortune
(e-mail address removed)

 

[Douglas J. Steele]

I use the following to calculate age from birthdate and today's date:
"=DateDiff("yyyy",[Birthdate],Now())+Int(Format(Now(),"mmdd")<Format([Birthdate],"mmdd"))".

Often for older birthdates the calculation yields a negative value, for
birthdate in 1918, age comes back as "-13".

What am I doing wrong?

I don't think the formula I use has that problem:

http://groups.google.com/group/microsoft.public.access/msg/5a1626d8b2777c23

Actually, it would suffer from the same problem, James, since the issue is
that the year is being stored as 2018, not 1918.

Incidentally, since the Format statement returns a string, you might
consider using CSng (or CDbl) in your formula:

Int(CSng(Format([CurrentDate],"yyyy.mmdd")) -
CSng(Format([BirthDate],"yyyy.mmdd")))

or (probably better)

Int(CDbl(Format([CurrentDate],"yyyy.mmdd")) -
CDbl(Format([BirthDate],"yyyy.mmdd")))


It's always a good idea to ensure that Access coerces strings to the correct
data type.

 

[James A. Fortune]

I use the following to calculate age from birthdate and today's date:
"=DateDiff("yyyy",[Birthdate],Now())+Int(Format(Now(),"mmdd")<Format([Birthdate],"mmdd"))".

Often for older birthdates the calculation yields a negative value, for
birthdate in 1918, age comes back as "-13".

What am I doing wrong?

I don't think the formula I use has that problem:

http://groups.google.com/group/microsoft.public.access/msg/5a1626d8b2777c23

Actually, it would suffer from the same problem, James, since the issue is
that the year is being stored as 2018, not 1918.

If the date is stored correctly, mine works. The mmdd format without
the yyyy may try to use the default Access behavior though to assign a
year. If the value was stored as 3/3/18 instead of 3/3/1918 then I'd
say fix the data first.

Incidentally, since the Format statement returns a string, you might
consider using CSng (or CDbl) in your formula:

Int(CSng(Format([CurrentDate],"yyyy.mmdd")) -
CSng(Format([BirthDate],"yyyy.mmdd")))

or (probably better)

Int(CDbl(Format([CurrentDate],"yyyy.mmdd")) -
CDbl(Format([BirthDate],"yyyy.mmdd")))


It's always a good idea to ensure that Access coerces strings to the correct
data type.

For a SQL string it doesn't matter that a string is returned since SQL
will treat it as an expression and treat the expression as a number
automatically. Note that I should have had single quotes instead of
double quotes for the SQL version. I did not have a problem when
testing this formula in VBA code (using recordset variables instead of
field names) but I suppose it wouldn't hurt to coerce it explicitly.
Feel free to put that in whenever you use my formula .

James A. Fortune
(e-mail address removed)

 

[Douglas J Steele]

Denis wrote:

I use the following to calculate age from birthdate and today's date:
"=DateDiff("yyyy",[Birthdate],Now())+Int(Format(Now(),"mmdd")<Format([Bir thdate],"mmdd"))".

Often for older birthdates the calculation yields a negative value, for
birthdate in 1918, age comes back as "-13".

What am I doing wrong?

I don't think the formula I use has that problem:

http://groups.google.com/group/microsoft.public.access/msg/5a1626d8b2777c2 3


Actually, it would suffer from the same problem, James, since the issue is
that the year is being stored as 2018, not 1918.

If the date is stored correctly, mine works. The mmdd format without
the yyyy may try to use the default Access behavior though to assign a
year. If the value was stored as 3/3/18 instead of 3/3/1918 then I'd
say fix the data first.

I agree that if the date is stored correctly, your approach will work (and
it's a neat variation of the usual formula)

However, if you read the thread, you'll see that the problem is that
entering 3/3/18 as the DOB resulted in it being stored as 3/3/2018.