Calculate Age at time of Death

[illingworth22]

I have a database, Access 2003, for patients in the Hospital where I work. The Main register has the following fields 1) Date_of_Birth and 2) Date_of_Death. I would like the Age to be calculated automaticly so I created a querie "Age Calculator" which has the two dates in and the following "Age: Year([Date_of_Death])-Year([Date of Birth])" formula.

The formula kind of works.... here is the main question. If someones DOB is 02/06/1936 and they pass away on 18/05/2010 they are actually 73 years old, but my formula tells me the patient is 74. Any ideas?

 

[JamesMcS]

Because you're only using the year part of each date. If you just use DOB and DOD it'll take the days into account and it should be OK. So:

Age: (dateserial([Date_of_Death])-dateserial([Date of Birth]))/365

Should give you their age in years and a decimal representing the fraction of the year. I think, anyway. Let me know what happens!

 

[gemma-the-husky]

something like

datediff("yyyy",dateofdeath, dateofbirth) is possibly more accurate.

 

[JamesMcS]

Would that not still give the answer in whole years? I thought of datediff but it's the fraction of the year the OP was after....

 

[rainman89]

Have you seen this?

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

Age: DateDiff("yyyy",[Date_of_Birth],[Date_Of_Death])+Int(Format([Date_Of_Death],"mmdd")<Format([Date_of_Birth],"mmdd"))

 

[boblarson]

Yep, Ray's got it.

 

[gemma-the-husky]

i thought he wanted just whole years

 

[boblarson]

i thought he wanted just whole years

And that is what that function returns. It just checks to see if the person has passed their birthday yet for this year and if so adds one to it.

 

[JamesMcS]

The original problem was that the OP's formula showed whole years only, I thought - where it didn't take into account the months and days. So dateserial'ing both and dividing by 365, I thought, would produce a number of years and, say, a .5 if it was 6 months.

 

[namliam]

The OPs question is "they are actually 73 years old, but my formula tells me the patient is 74"

To which the answer is, get a proper Age function.
Though the Int is not needed as the resulting boolean value is an integer already.

/365 for an age calculation makes for (small) mistakes due to the leap years, which over 80 years account for 20 days of difference, perhaps /365.25 ..... but yeah
Rainman's is distinctly better IMNSHO

 

[illingworth22]

Have you seen this?

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

Age: DateDiff("yyyy",[Date_of_Birth],[Date_Of_Death])+Int(Format([Date_Of_Death],"mmdd")<Format([Date_of_Birth],"mmdd"))

Worked a treat Thanks verry much! Now all I have to do is figure out how to put the data (Age) into the form

 

[JamesMcS]

Rainman's was an interesting way of doing it, didn't think of that - I was just going to go for formatting the result of the dateserial calculations (divided by 365.25, forgot about damn leap years) as yy, mm, dd and get it that way. But whatever gets it working for ya

 

[illingworth22]

Worked a treat Thanks verry much! Now all I have to do is figure out how to put the data (Age) into the form

OK can I ask one more question? It is part of this problem and I am a novice and this is not for profit!
I created a Query called Age Calculator and I now have my 3 fields in, DOB, RIP & the Age (calculated from the above formula).
When I go back to my main Register Table and add a field Age and then select the query to look up data and click on the Age Calculator query I get the following error
"No valid fileds can be found in 'Age Calculator' You may have to select a query that uses the table your adding the lookup columb to. Please select a new source.
The Query was a simple query, should I have made a cross tab one instead?
Any suggestions?

 

[DCrake]

This peice of code will calculate it down to years, months and days

Function fAgeYMD(StartDate As Date, EndDate As Date) As String
'Purpose:   Returns the difference between StartDate and EndDate in full years, months and days
'To call:
' ? fAgeYMD(DOB,DOD)


Dim inthold As Integer
Dim dayHold As Integer

   inthold = Int(DateDiff("m", StartDate, EndDate)) + _
             (EndDate < DateSerial(year(EndDate), month(EndDate), Day(StartDate)))

   If Day(EndDate) < Day(StartDate) Then
      dayHold = DateDiff("d", StartDate, DateSerial(year(StartDate), month(StartDate) + 1, 0)) + Day(EndDate)
   Else
      dayHold = Day(EndDate) - Day(StartDate)
   End If
   
   fAgeYMD = Int(inthold / 12) & " year" & IIf(Int(inthold / 12) <> 1, "s ", " ") _
             & inthold Mod 12 & " month" & IIf(inthold Mod 12 <> 1, "s ", " ") _
             & LTrim(str(dayHold)) & " day" & IIf(dayHold <> 1, "s", "")

End Function

 

[namliam]

format( EndDate - StartDate , " YY ""year "" MM "" month "" dd "" day "" ")

Its missing the "fancy" s for multiples but.... it seems much simpler?

 

[rainman89]

Rainman's was an interesting way of doing it, didn't think of that - I was just going to go for formatting the result of the dateserial calculations (divided by 365.25, forgot about damn leap years) as yy, mm, dd and get it that way. But whatever gets it working for ya

I take no credit for it. All i did was search until I found the correct answer!

 

[JamesMcS]

*Don't tell anyone but formatting it like I said didn't work*