Calculating Age

[twitchie]

I've search and found several threads on this, but none seemed to fit with what I'm doing. I'm updating a database created by a guy who retired 6+ years ago. His formula for determining the person's age using today's date and the person's date of birth adds a year most every time. I've created a temporay patch, but I'm afraid it's not full proof so would love some input.

On a form, we enter a person's DOB (date of birth). A report is run which has a query with relationships as its control source. In that query, there are these functions, among other control sources:

AgeDays: Now()-[DOB]
AgeInMonths: [AgeDays]/30.4375
AgeYears: IIf([AgeInMonths]<12,0,[AgeInMonths/12)
AgeMonths: IIf([AgeInMonths]>12,[AgeInMonths] Mod 12,[AgeInMonths])

The work around I made is below (changes in 3rd line)

AgeDays: Now()-[DOB]
AgeInMonths: [AgeDays]/30.4375
AgeYears: IIf([AgeInMonths]<12,0,(([AgeInMonths/12)-1))
AgeMonths: IIf([AgeInMonths]>12,[AgeInMonths] Mod 12,[AgeInMonths])

Is there osmething standing out here as just a simple math error to make it add a year to peoples' age? Maybe if they've laready had a birthday this year or something? TIA!!

 

[RuralGuy]

Isn't their age in years adequate for your function?
DateDiff("yyyy", [DateofBirth], Date())+ Int( Format(Date(), "mmdd") < Format( [DateofBirth], "mmdd") )

 

[twitchie]

Isn't their age in years adequate for your function?
DateDiff("yyyy", [DateofBirth], Date())+ Int( Format(Date(), "mmdd") < Format( [DateofBirth], "mmdd") )

Thanks for the quick reply. We actually have a field for years and a field for months, so just years wouldn't do it. We need the years and months.

 

[HiTechCoach]

This function may help:

Function AgeCount4(varDOB As Variant, varDate As Variant) As String

On Error GoTo Err_AgeCount4
'
' PURPOSE:  Determines the difference between two dates in years,
'           months and days.
'
' ARGUMENTS:  (will accept either dates (e.g., #03/24/00#) or
'              strings (e.g., "03/24/00")
'  varDOB:  The earlier of two dates.
'  varDate: The later of two dates.
'
' RETURNS:  A string as years.months.days, e.g., (17.6.21)
'
' CODED BY: raskew
'
' NOTES:    To test:  Type '? agecount4("03/04/83", "03/23/00")
'                     in the debug window. The function will
'                     return "17.0.19".

Dim dteDOB As Date, dteDate As Date, dteHold As Date
Dim intOldyears As Integer, intOldMonths As Integer, intOldDays As Integer
Dim intNuYears As Integer, intNuMonths As Integer, intNudays As Integer
Dim intYears As Integer, intMonths As Integer, intDays As Integer

If IsDate(varDOB) And IsDate(varDate) Then
   dteDOB = DateValue(varDOB)
   dteDate = DateValue(varDate)
   
  'Reverse the dates if they were input backwards
   If dteDOB > dteDate Then
      dteHold = dteDOB
      dteDOB = dteDate
      dteDate = dteHold
   End If

   intOldyears = Year(dteDOB)
   intOldMonths = Month(dteDOB)
   intOldDays = Day(dteDOB)
   intNuYears = Year(dteDate)
   intNuMonths = Month(dteDate)
   intNudays = Day(dteDate)

   If intNudays < intOldDays Then
      intNudays = intNudays + 30
      intNuMonths = intNuMonths - 1
   End If

   If intNuMonths < intOldMonths Then
      intNuMonths = intNuMonths + 12
      intNuYears = intNuYears - 1
   End If

   intYears = intNuYears - intOldyears
   intMonths = intNuMonths - intOldMonths
   intDays = intNudays - intOldDays

   AgeCount4 = LTrim(Str(intYears)) & "." & LTrim(Str(intMonths)) & "." & LTrim(Str(Int(intDays)))
Else
   MsgBox "Invalid date parameters -- please try again", vbOKOnly, "Check input dates!"
   GoTo Exit_AgeCount4
End If

Exit_AgeCount4:
    Exit Function

Err_AgeCount4:
    Select Case Err.Number
    Case 0
        Resume Next
    Case Else
        MsgBox Err.Number & ": " & Err.Description
    End Select
    Resume Exit_AgeCount4
End Function