Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2004
    Location
    columbus, ohio
    Posts
    6

    Question 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. #2
    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. #3
    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. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    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)))
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    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...
    All code ADO/ADOX unless otherwise specified.
    Mike.

  6. #6
    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. #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
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

  8. #8
    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.


    Quote Originally Posted by jst1lady View Post
    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. #9
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by jst1lady View Post

    ...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)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Or better yet, push that code into a public function in a code module and use it wherever and whenever required. In forms, reports queries....
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •