Age Display Error

[ritland]

I have a listing of birthdays in a database and I want to calculate
their age as of the current year regardless of what specific day/month
it is. I am using the following formula as found on http://allenbrowne.com/func-08.html:

=DateDiff("y","[DOB]",Date())

The textbox format for age on the report is set to "yy". For most
individuals the age dispalys correctly. However, if a person was born
on or after 1979 (or less then 30 years old) the age displays as a
"19XX" with XX being their age.

For instance:
=DateDiff("y","1/1/1978",Date()) = 30

However:
=DateDiff("y","1/1/1979",Date()) = 1929

Does anyone know what would make this happen?

 

[fredg]

I have a listing of birthdays in a database and I want to calculate
their age as of the current year regardless of what specific day/month
it is. I am using the following formula as found on http://allenbrowne.com/func-08.html:

=DateDiff("y","[DOB]",Date())

I doubt you would find this expression on Allen's web site. It is
completely incorrect. (I just looked and it is NOT there. The closest
thing to your above expression that I could find there
DateDiff("y", DOB, Date) is in the section of expressions that is
clearly marked as "These examples do not work reliably:")

The textbox format for age on the report is set to "yy".

The Format of the DOB value is irrelevant.

For most

individuals the age dispalys correctly. However, if a person was born
on or after 1979 (or less then 30 years old) the age displays as a
"19XX" with XX being their age.

For instance:
=DateDiff("y","1/1/1978",Date()) = 30

However:
=DateDiff("y","1/1/1979",Date()) = 1929

Where do you come up with a value of 1929 ?
using the above formula it should be 10676.
?DateDiff("y","1/1/1979",Date())
10676

The correct value, in years, using your dates should be 29.
?DateDiff("yyyy",#1/1/1979#,Date())
29

Does anyone know what would make this happen?

You have several problems here.
1) =DateDiff("Y", etc..) is not going to return a value in years.
To calculate a difference in years, you need to use "yyyy", not "y".
Look up the DateDiff function in VBA help to find the various
arguments needed.

2) The values must be date values.
"1/1/1978" is text (as is your "[DOB]"), not date.
Date values must be enclosed within the date delimiter symbol #.
So #1/1/1978# will be accepted by Access as a data value, as will
[DOB] (without the quotes around it if [DOB] is a Date datatype
field).

3) =DateDiff("yyyy",#1/1/1978#,Date()) returns 30.

4) The DateDiff function calculates the boundary crossings between 2
dates, so using the datediff function
=DateDiff("yyyy",#12/31/2007#,1/1/2008#)
will return 1 (year), which is correct, it crosses the boundary
between 2007 and 2008 once, even though it is actually just one day.

However, if you are calculating ages, a person does not get 1 year
older on New Years Day, so you must include, within the calculation
whether or not the person's date of birth has already occurred in the
current year.

5) Here is an expression that will accurately compute age.

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

Directly as the control source of an unbound control:
=DateDiff("yyyy",[DOB],Date())-IIf(Format([DOB],"mmdd")>Format(Date(),
"mmdd"),1,0)

You do know, I hope, that this Age computation should NOT be stored in
any table.
Just compute it and display it on a form or report, as needed.

I hope this has helped.

 

[ritland]

I have a listing of birthdays in a database and I want to calculate
their age as of the current year regardless of what specific day/month
it is. I am using the following formula as found onhttp://allenbrowne.com/func-08.html:

=DateDiff("y","[DOB]",Date())

I doubt you would find this expression on Allen's web site. It is
completely incorrect. (I just looked and it is NOT there. The closest
thing to your above expression that I could find there
DateDiff("y", DOB, Date) is in the section of expressions that is
clearly marked as "These examples do not work reliably:")

The textbox format for age on the report is set to "yy".

The Format of the DOB value is irrelevant.

For most

individuals the age dispalys correctly. However, if a person was born
on or after 1979 (or less then 30 years old) the age displays as a
"19XX" with XX being their age.

For instance:
=DateDiff("y","1/1/1978",Date())    =   30

However:
=DateDiff("y","1/1/1979",Date())    =   1929

Where do you come up with a value of 1929 ?
using the above formula it should be 10676.
?DateDiff("y","1/1/1979",Date())
 10676

 The correct value, in years, using your dates should be 29.
 ?DateDiff("yyyy",#1/1/1979#,Date())
 29

Does anyone know what would make this happen?

You have several problems here.
1) =DateDiff("Y", etc..) is not going to return a value in years.
To calculate a difference in years, you need to use "yyyy", not "y".
Look up the DateDiff function in VBA help to find the various
arguments needed.

2) The values must be date values.
"1/1/1978" is text (as is your  "[DOB]"), not date.
Date values must be enclosed within the date delimiter symbol #.
So #1/1/1978# will be accepted by Access as a data value, as will
[DOB] (without the quotes around it if [DOB] is a Date datatype
field).

3) =DateDiff("yyyy",#1/1/1978#,Date()) returns 30.

4) The DateDiff function calculates the boundary crossings between 2
dates, so using the datediff function
=DateDiff("yyyy",#12/31/2007#,1/1/2008#)
will return 1 (year), which is correct, it crosses the boundary
between 2007 and 2008 once, even though it is actually just one day.

However, if you are calculating ages, a person does not get 1 year
older on New Years Day, so you must include, within the calculation
whether or not the person's date of birth has already occurred in the
current year.

5) Here is an expression that will accurately compute age.

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

Directly as the control source of an unbound control:
=DateDiff("yyyy",[DOB],Date())-IIf(Format([DOB],"mmdd")>Format(Date(),
"mmdd"),1,0)

You do know, I hope, that this Age computation should NOT be stored in
any table.
Just compute it and display it on a form or report, as needed.

I hope this has helped.

 

[ritland]

Thanks so much! I am working through your formula now. Just one thing,
I probably was not clear but I do need the difference in years between
DOB year and current year regardless of the month and day for the DOB/
current year.

I have a listing of birthdays in a database and I want to calculate
their age as of the current year regardless of what specific day/month
it is.

e.g. DOB=12/1/07 Current date= 3/25/08 formula would output 1 year
because this is how old they will be on their birthday in the current
year.

Thanks again!

 

[ritland]

Oh, by the way, this is how I plan to use the formula. I am creating a
birthday list that would be generated a month before the birth month
but would display their age as if the birthday had already taken
place. IE The current month is March and I would create a birthday
list for everyone born in April. The report would list the age they
will be on their birthday, not their current age. So the DOB Year -
Current Year would give me the correct age. The only problem I see in
this is that if the current month is December the age would be a year
off for the birthday list for January. In generating a birthday list
for January in December the formula would need to be DOB Year -
Current Year+1. All the best!