ACC2000: Two Functions to Calculate Age in Months and Years (210522)

MORE INFORMATION

Creating the Functions

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

'*************************************************************
' FUNCTION NAME: Age()
'
' PURPOSE:
'    Calculates age in years from a specified 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 specified date.
'  If the specified 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

1. By using the Date/Time tool in Control Panel, make a note of the current date, and then set the date to June 3, 2000.
2. Open a module or create a new one.
3. On the View menu, click Immediate Window.
4. Assume your friend's birth date was November 15, 1967 and today is June 3, 2000. Type the following line in the Immediate window, and then press ENTER:

   ?Age("11/15/67")
   						

   Note that Microsoft Access responds with the value 32 (years).
5. Type the following line, and then 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 32 years and six months old.
6. By using the Date/Time tool in Control Panel, reset the date to the current date that you noted in step 1.

Using the Age() and AgeMonths() Functions

1. In the sample database Northwind.mdb, type 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 line in the ControlSource property of the new text box control:

   =Age([OrderDate]) & " yrs " & AgeMonths([OrderDate]) & " mos"

4. View the form in Form view. Note that the age of the order is displayed in the new text box control.