ACC: Two Functions to Calculate Age in Months and Years

ID: Q100136

The information in this article applies to:

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article shows you how to create a function to calculate the age of a person or thing based on a given date.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0

NOTE: A demonstration of the technique used in this article can be seen in the sample file, Qrysmp97.exe. For information about how to obtain this sample file, please see the following article in the Microsoft Knowledge Base: 

   ARTICLE-ID: Q182568
   TITLE     : ACC97: Microsoft Access 97 Sample Queries Available on
               MSL

MORE INFORMATION

Enter the following code in a module:

NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic. 

   '==========================================================
   ' General Declaration
   '==========================================================
    Option Explicit

   '*************************************************************
   ' FUNCTION NAME: Age()
   '
   ' PURPOSE:
   '    Calculates age in years from a given date to today's date.
   '
   ' INPUT PARAMETERS:
   '    StartDate: The beginning date (for example, a birth date).
   '
   ' RETURN
   '    Age in years.

   '*************************************************************
    Function Age (varBirthDate As Variant) As Integer
       Dim varAge As Variant

      If IsNull(varBirthdate) then Age = 0: Exit Function

      varAge = DateDiff("yyyy", varBirthDate, Now)
      If Date < DateSerial(Year(Now), Month(varBirthDate), _
                           Day(varBirthDate)) Then
         varAge = varAge - 1
      End If
      Age = CInt(varAge)
    End Function

    '*************************************************************
    ' FUNCTION NAME: AgeMonths()
    '
    ' PURPOSE:
    '  Compliments the Age() function by calculating the number of months
    '  that have expired since the last month supplied by the given date.
    '  If the given date is a birthday, the function returns the number of
    '    months since the last birthday.
    '
    ' INPUT PARAMETERS:
    '    StartDate: The beginning date (for example, a birthday).
    '
    ' RETURN
    '    Months since the last birthday.

    '*************************************************************
    Function AgeMonths(ByVal StartDate As String) As Integer
      Dim tAge As Double
      tAge = (DateDiff("m", StartDate, Now))
      If (DatePart("d", StartDate) > DatePart("d", Now)) Then
          tAge = tAge - 1
      End If

      If tAge < 0 Then
         tAge = tAge + 1
      End If

      AgeMonths = CInt(tAge Mod 12)

    End Function

Testing the Age() and AgeMonths() Functions

To test the Age() and AgeMonths() functions, follow these steps:

1. Open a module or create a new one.

2. On the View menu, click Debug Window (or Immediate Window in Microsoft 

   Access 2.0 or earlier).
3. Assume your friend's birth date was November 15, 1967 and today is 
   June 3, 1993. Type the following in the Debug window and press ENTER:

      ? Age("11/15/67")

   Note that Microsoft Access responds with the value 25 (years).
4. Type the following and press ENTER: 

      ? AgeMonths("11/15/67")

   Note that Microsoft Access responds with the value 6, indicating that
   six months have passed since this person's last birthday. Your friend
   is 25 years and six months old.

Using the Age() and AgeMonths() Functions

The following procedure explains how to mark old orders by placing the age value in a new control:

1. In the sample database Northwind.mdb (or NWIND.MDB in Microsoft Access 

   2.0 or earlier) enter the Age() and AgeMonth() functions in a new
   module.
2. Open the Orders form in Design view and add an unbound text box control.

3. Type the following in the ControlSource property of the new text box 

   control:

      =Age([OrderDate]) & " ys " & AgeMonths([OrderDate]) & " mo"

    NOTE: In versions 1.x and 2.0, there is a space in Order Date field
    name.
4. View the form in Form view. Note that the age of the order is displayed 
   in the new text box control.

REFERENCES

For more information about date differences, search the Help Index for "DateDiff," or ask the Microsoft Access 97 Office Assistant.

Additional query words: birth birthday birthdate age abc elapsed time 

Keywords          : kbprg
Version           : 1.0 1.1 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto

Last Reviewed: November 20, 1998