Calculating Age between Form and Subform

[pablotx]

Hello to all:

Hopefully a simple question for you advanced users out there....

I have a main form tracking Deaths [FrmDeath]. It has all of the particulars about a patient's death. On it there is a patient ID # [patientID]. It pulls up the consumer name and demographics on a subform [subfrmPatient].

I would like to have a field on my form and possibly in my main Death table, to calculate the patient's age at death. From [FrmDeath].[Date of Death] and the subform [subfrmPatient].[Birthdate].

I thought I had it figured out, but still am unsuccessful. Any help would be greatly appreciated.

Thanks in advance.

 

[boblarson]

This is how you would calculate the age at death:

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

That is for a query and where the fields are all in the same query. You should be able to modify it to use that calculation for a control source of a control but it depends on where you want it as to the syntax.

 

[YNWA]

I currently use this module. It returns an age in Years, Months and Days.

fldDOB is on my main form, whilst fldDateDeath is on my subform.

Function fAgeYMD(fldDOB As Date, fldDateDeath As Date) As String
'Purpose:   Returns the difference between StartDate and EndDate in full years, months and days
'Coded by:  raskew
'To call:
' ? fAgeYMD(#7/6/54#, #10/3/84#)
'Returns:
' 30 years 2 months 28 days
Dim inthold As Integer
Dim dayHold As Integer
   inthold = Int(DateDiff("m", fldDOB, fldDateDeath)) + _
             (fldDateDeath < DateSerial(Year(fldDateDeath), Month(fldDateDeath), Day(fldDOB)))
   If Day(fldDateDeath) < Day(fldDOB) Then
      dayHold = DateDiff("d", fldDOB, DateSerial(Year(fldDOB), Month(fldDOB) + 1, 0)) + Day(fldDateDeath)
   Else
      dayHold = Day(fldDateDeath) - Day(fldDOB)
   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

I then put a field in on subform to show the Age at Death. In the query that the subform is linked to, I put a field as such:

Find Age: fAgeYMD([fldDOB],[fldDateDeath])

This called the module above and displays the age in the field on the subform.