Age Calculation

[BradW]

I have previously done age calculations based on a formula such as:

Trunc(([Contact Date]-[DOB])/365.25)

However in Access 2002 this does not work as the program says that it does not recognize the Truncate function. I need to calcualte an age at a fixed time and I have a contact date field and a date of birth field, I want to know how old a person was on the date of contact. Without the Truncate function everything that I try rounds and this is unacceptable.

Thanks in advance for all help.

 

[dhookom]

This is more accurate.
Age=DateDiff("yyyy", [Bdate], Now())+ _
Int( Format(now(), &quot;mmdd&quot < Format( [Bdate], &quot;mmdd&quot )

Duane
MS Access MVP

 

[BradW]

Thanks for your quick help. I used the formula and it works, but in my tests it is still rounding the year, if the person is more than half way through the year between birthdays it rounds them up to the next age??? Am I doing something wrong here?

 

[JonFer]

BradW - the Int() function returns the integer portion of a number without rounding so that should replace Trunc. The CInt() function rounds so avoid it.

 

[BradW]

Thank you, that was exactly what I was looking for!

 

[BobJacksonNCI]

I know Brad's question has already been answered, but I needed a break from my project and was looking through the posts.

Following is the shortest and least complicated formula that accurately calculates one's age that I've found...

Int(((Date()-[BirthdayFieldName])+1)/365.2425)



HTH,
Bob
Your mileage may vary, but following the guidelines in faq181-2886 will help you reach your goal.

 

[dhookom]

Bob,
I'm sure there are cases when this will not be accurate since not all years have the same number of days etc. For instance on 2/28/2008 a person born on 2/29/2004 should be 3 years old and your expression returns 4. The expression that I got from

http://www.mvps.org/access

accurately returns 3.

I doubt this will affect a lot of instances but if you want absolute accuracy, it's best to use the 100% accurate function.

Duane
MS Access MVP

 

[BradW]

Bob, why the +1 in your formula? I have been using the same thing basically, but without the +1 part?

 

[BobJacksonNCI]

Duane,

I sit corrected!

On leap year anniversary dates, people born on February 28th will INCORRECTLY be reported as being a year older than they actually are if the formula I posted is used.

For absolute accuracy, one should use your formula.

 

[BobJacksonNCI]

BradW,

The +1 is to indicate one is a year older ON their anniversary. Without it, the age doesn't consider the birthday passed until the following day.

 

[BradW]

Thanks much, Bob!

 

[BobJacksonNCI]

BradW and Duane,

I preferred my formula because of it's simplicity...
Until Duane illustrated the error.

Partly because I don't understand his formula and partly because I think mine should work, what do you think about:

Int(((Date()-[BirthdayFieldName])+.5)/365.2425) ?

TIA,
Bob

 

[JonFer]

Duane's formula does this:

DateDiff calculates just the year difference and ignores everything else. This difference is then adjusted by the results of the comparison which produces a 0 (False) if the birthday month and day have already passed and a -1 (True) if the birthday hasn't occurred yet.

Very clever.

 

[dhookom]

There isn't too much difference between entering:
DateDiff(&quot;yyyy&quot;, [Bdate], Now())+ Int( Format(now(), &quot;mmdd&quot < Format( [Bdate], &quot;mmdd&quot )
or
Int(((Date()-[BirthdayFieldName])+.5)/365.2425)

I would actually create a small function GetAge() based on my expression and then you could use:
GetAge([DOBField])
There is a more elaborate function at

http://www.mvps.org/access/datetime/date0001.htm

Duane
MS Access MVP