CODEWITHSUNDEEP
databasems-accessvbams-access-2010
Alph
Alph

Reputation: 391

calculating age in years and months in Microsoft Access (2010)

I have two fields (Date of Physical Exam and Date of Birth). I calculated the age in years((Date of Physical Exam- Date of Birth)/365.25). What I want to do is calculate age in years and months in separate field. I am not sure if it can be done using code builder or some way.

Upvotes: 1

Views: 10010

Answers (2)

Gord Thompson
Gord Thompson

Reputation: 123474

While the DateDiff() function seems like the logical choice for calculating ages, it unfortunately does not calculate the number of full years or months that have elapsed between two dates. For example, say that a baby was born on December 31, 2014, and was examined exactly 48 hours later, on January 2, 2015. That is,

DateOfBirth = DateSerial(2014, 12, 31)
DateOfExam = DateSerial(2015, 1, 2)

If we simply used DateDiff() to calculate her "age" in years and months at the time of the exam we would get

?DateDiff("yyyy", DateOfBirth, DateOfExam)
 1 
?DateDiff("m", DateOfBirth, DateOfExam)
 1

So, we would report that the baby is 1 year and 1 month old, when really she is just 2 days old.

Proper age calculations need to be more sophisticated than that. The following VBA function will calculate the "age" in years and months, returning a string like "2 years and 1 month":

Public Function AgeInYearsAndMonths(StartDate As Variant, EndDate As Variant) As Variant
    Dim Date1 As Date, Date2 As Date
    Dim mm1 As Integer, dd1 As Integer, mm2 As Integer, dd2 As Integer
    Dim ageYears As Integer, ageMonths As Integer, rtn As Variant
    rtn = Null
    If Not (IsNull(StartDate) Or IsNull(EndDate)) Then
        If StartDate <= EndDate Then
            Date1 = StartDate
            Date2 = EndDate
        Else
            Date1 = EndDate
            Date2 = StartDate
        End If
        mm1 = Month(Date1)
        dd1 = Day(Date1)
        mm2 = Month(Date2)
        dd2 = Day(Date2)
        ageYears = DateDiff("yyyy", Date1, Date2)
        If (mm1 > mm2) Or (mm1 = mm2 And dd1 > dd2) Then
            ageYears = ageYears - 1
        End If
        ageMonths = DateDiff("m", Date1, Date2) Mod 12
        If dd1 > dd2 Then
            If ageMonths = 0 Then
                ageMonths = 12
            End If
            ageMonths = ageMonths - 1
        End If
        If ageYears = 0 And ageMonths = 0 Then
            rtn = "less than 1 month"
        Else
            rtn = ageYears & " year" & IIf(ageYears = 1, "", "s") & " and " & ageMonths & " month" & IIf(ageMonths = 1, "", "s")
        End If
    End If
    AgeInYearsAndMonths = rtn
End Function

Upvotes: 6

Gustav
Gustav

Reputation: 55816

It takes a little more to take care of the finer details (as does a year calculation):

Public Function Months( _
  ByVal datDate1 As Date, _
  ByVal datDate2 As Date, _
  Optional ByVal booLinear As Boolean) _
  As Integer

' Returns the difference in full months between datDate1 and datDate2.
'
' Calculates correctly for:
'   negative differences
'   leap years
'   dates of 29. February
'   date/time values with embedded time values
'   negative date/time values (prior to 1899-12-29)
'
' Optionally returns negative counts rounded down to provide a
' linear sequence of month counts.
' For a given datDate1, if datDate2 is decreased stepwise one month from
' returning a positive count to returning a negative count, one or two
' occurrences of count zero will be returned.
' If booLinear is False, the sequence will be:
'   3, 2, 1, 0,  0, -1, -2
' If booLinear is True, the sequence will be:
'   3, 2, 1, 0, -1, -2, -3
'
' If booLinear is False, reversing datDate1 and datDate2 will return
' results of same absolute Value, only the sign will change.
' This behaviour mimics that of Fix().
' If booLinear is True, reversing datDate1 and datDate2 will return
' results where the negative count is offset by -1.
' This behaviour mimics that of Int().

' DateAdd() is used for check for month end of February as it correctly
' returns Feb. 28. when adding a count of months to dates of Feb. 29.
' when the resulting year is a common year.
'
' 2010-03-30. Cactus Data ApS, CPH.

  Dim intDiff   As Integer
  Dim intSign   As Integer
  Dim intMonths As Integer

  ' Find difference in calendar months.
  intMonths = DateDiff("m", datDate1, datDate2)
  ' For positive resp. negative intervals, check if the second date
  ' falls before, on, or after the crossing date for a 1 month period
  ' while at the same time correcting for February 29. of leap years.
  If DateDiff("d", datDate1, datDate2) > 0 Then
    intSign = Sgn(DateDiff("d", DateAdd("m", intMonths, datDate1), datDate2))
    intDiff = Abs(intSign < 0)
  Else
    intSign = Sgn(DateDiff("d", DateAdd("m", -intMonths, datDate2), datDate1))
    If intSign <> 0 Then
      ' Offset negative count of months to continuous sequence if requested.
      intDiff = Abs(booLinear)
    End If
    intDiff = intDiff - Abs(intSign < 0)
  End If

  ' Return count of months as count of full 1 month periods.
  Months = intMonths - intDiff

End Function

Upvotes: 1

Related Questions