# Thread: Calculating Age of patients at time of death

1. Registered User
Join Date
Mar 2004
Location
columbus, ohio
Posts
6

## Unanswered: Calculating Age of patients at time of death

=DateDiff("yyyy",[birth],[death])+IIf([death],"mmdd"<[birth],"mmdd"-1)

This is the command I am using to find out the age of a patient. The formula works only if the date of death is after the bday. I put the iif function in to say that if the date of death is before the birthday subtract one year. It will not subtract one year off the age for those patients meeting that criteria. Please help! What am I doing wrong? Thanks

Donielle

2. Registered User
Join Date
Jun 2003
Location
Belgium
Posts
133
Why on earth do you try to calculate an age of someone who has died before he was born?
Most likely, the dates were not entered correctly.
Never try to correct mistakes by making another one.

You should do this :
-make sure it is impossible to enter date of death that are before the bday so no one can make the mistake in the future.
-when calculating the age, use this formula :

=IIF([death]<[birth],"ERROR",DateDiff("yyyy",[birth],[death]))

This will give you either the age of the patient, or the text "ERROR" to indicate that calculating the age is not possible.

3. Registered User
Join Date
Mar 2004
Location
columbus, ohio
Posts
6
I am calculating the age if the person dies before he/she has another bday for that year of death. If someone is born on 6/18/1947 but dies 5/1/2003 he is 55 years old. My formula put that he was 56. So that is why I am calculating the death of patient not before he was born but before his bday. THanks

Originally posted by Herman
Why on earth do you try to calculate an age of someone who has died before he was born?
Most likely, the dates were not entered correctly.
Never try to correct mistakes by making another one.

You should do this :
-make sure it is impossible to enter date of death that are before the bday so no one can make the mistake in the future.
-when calculating the age, use this formula :

=IIF([death]<[birth],"ERROR",DateDiff("yyyy",[birth],[death]))

This will give you either the age of the patient, or the text "ERROR" to indicate that calculating the age is not possible.

4. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Donielle, you're on the right track, you just got the first parameter of the IIF wrong

it has to be an expression such as X < Y

in your IIF, the first parameter is [death] which, since it's non-zero, will evaluate as true (i think) and so the result of the IIF is "mmdd"<[birth], which, since it's comparing a string to a date, will give you a zero (true) or a -1 (false)...

... or something like that

anyhow, try this, it's a bit long winded but does not utilize strings for mmdd (which may be a tad slower)

= year(death)
- year(birth)
- iif(month(death)>month(birth),0,
iif(month(death)<month(birth),1,
iif(day(death)<day(birth),1,0)))

5. Registered User
Join Date
Sep 2003
Location
T.O.
Posts
326
You can use the following functions:

Month()
Day()
and
CLng()

in the following manner to determine what you are looking for:
IIf((CLng(Format([DeathDate],"m") & Format([DeathDate],"dd"))<CLng(Format([BirthDate],"m") & Format([BirthDate],"dd"))),DateDiff("yyyy",[BirthDate],[DeathDate])-1,DateDiff("yyyy",[BirthDate],[DeathDate]))

Basically you are taking June 18 DOB, converting it to 618 and comparing it to May 1 DOD, 510, finding that it's larger and subtracting a year.

Use dd instead of d because in your comp, you want May 12 (512/512) to be less than June 1 (601/61).

BTW, your question was obvious and I'm not sure what the miniflame is based on...

6. Registered User
Join Date
Mar 2004
Location
columbus, ohio
Posts
6
THank you.
I found that this formula works great...
=DateDiff("yyyy",[birth],[death])+IIf(Format([death],"mmdd")<Format([birth],"mmdd"), -1, 0

However, the age is automatically calculated and put into the field on the form...now when I query just to see the demographics of all the patients in the database for some reason the ages all show 0 but on the form view it shows the correct ages. The reports I tried to create also showed the ages of everyone to be "0". What am I doing wrong now? Is there something I should be putting in the design view of the query to make it show the age? I usually do the report wizard because I am no where near being at the pro level.

7. Join Date
Jun 2003
Location
USA
Posts
1,032
Here is a function to calculate age. The code could be placed in any module.

An example of use for a control's ControlSource property would be:
=AgeInYears([DOBField], Date())

An example of use for a calculated field in a query would be:
AgeCalcField: AgeInYears([DOBField], Date())

Public Function AgeInYears(ByVal pvarBirthDate As Variant, ByVal pvarCompareDate As Variant) As Long
' Purpose: Calc age in years as of the compare date.
' Accepts: pvarBirthDate date
' pvarCompareDate date
' Returns: number
' Remarks:
' Returns 0 if any needed fields blank.
'
' A simple DateDiff("yyyy",[pvarBirthDate],[pvarCompareDate]) is off
' by one when the month & day of the birthdate is greater than
' the month & day of the compare date.
'
' Access stores the date AND time in each of the date/time fields.
' So even though the fields may be formatted to LOOK just like
' date only, there also is a time stored.
' And the time stored is usually a constant 12:00:00 AM
' (effectively 0) which can be seen if you set the field to be
' long time format. Or even in General Date format any time but
' 12:00:00 AM is shown. It's usually a good idea to store a current date
' using Date() instead of Now() unless one really does want a time
' other than 12:00:00 AM to be stored.

On Error GoTo Err_AgeInYears

' Exit if some fields null.
If IsNull(pvarBirthDate) Or IsNull(pvarCompareDate) Then
AgeInYears = 0
Exit Function
End If

' Calc age in years and subtract 1 if the birth month/day is not on or before the
' compare month/day.
' (I.e. 8/1/1995 birthdate and 9/1/1996 compare date would be age of 1
' and 9/1/1995 birthdate and 9/1/1996 compare date would be age of 1
' but 9/2/1995 birthdate and 9/1/1996 compare date would be age of 0.)
If (Month(pvarBirthDate) < Month(pvarCompareDate)) Or (Month(pvarBirthDate) = Month(pvarCompareDate) And Day(pvarBirthDate) <= Day(pvarCompareDate)) Then
AgeInYears = DateDiff("yyyy", pvarBirthDate, pvarCompareDate)
Else
AgeInYears = DateDiff("yyyy", pvarBirthDate, pvarCompareDate) - 1
End If

Exit Function

Err_AgeInYears:
MsgBox "Error " & Err & "." & Chr(13) & Chr(10) & Chr(10) & Err.Description & ".", vbExclamation
Exit Function
End Function

8. Registered User
Join Date
Oct 2014
Posts
1

## Calculate Age in Access Form

Hi,

I am trying to accomplish in an Access form what the code below in red accomplishes in a query. I got it to work in a query, but it won't in the Form. Thanks.

THank you.
I found that this formula works great...
=DateDiff("yyyy",[birth],[death])+IIf(Format([death],"mmdd")<Format([birth],"mmdd"), -1, 0

However, the age is automatically calculated and put into the field on the form...now when I query just to see the demographics of all the patients in the database for some reason the ages all show 0 but on the form view it shows the correct ages. The reports I tried to create also showed the ages of everyone to be "0". What am I doing wrong now? Is there something I should be putting in the design view of the query to make it show the age? I usually do the report wizard because I am no where near being at the pro level.

9. Moderator
Join Date
Jun 2005
Location
Richmond, Virginia USA
Posts
2,764

...now when I query just to see the demographics of all the patients in the database for some reason the ages all show 0 but on the form view it shows the correct ages...
This is a calculated field, and calculated fields have to be re-calculated whenever they are needed, whether in another Form, Query or Report.

In Query Design View, in a new, blank field, you'd use something like this:

Code:
`AgeAtDeath:DateDiff("yyyy",[birth],[death])+IIf(Format([death],"mmdd")<Format([birth],"mmdd"), -1, 0`

Linq ;0)>

Join Date
Nov 2004
Location
out on a limb
Posts
13,692