This article shows you how to create two functions that you
can use to calculate the age of a person or thing based on a specified
date.
Note You can see a demonstration of the technique that is used in this
article in the sample file Qrysmp00.exe. For additional information
about how to obtain this sample file, click the following article number to
view the article in the Microsoft Knowledge Base:
207626
(http://support.microsoft.com/kb/207626/
)
ACC2000: Access 2000 sample queries available in Download Center
'==========================================================
' 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
To test the Age() and AgeMonths() functions, follow these steps.
IMPORTANT: The following steps ask you to change the date on your computer.
Make sure that you complete step 6 to reset the date to the current date.
By using the Date/Time tool in Control Panel, make a note of the current date, and then
set the date to June 3, 2001.
Open a module or create a new one.
On the View menu, click Immediate Window.
Assume your friend's birth date was November 15, 1967 and
today is June 3, 2001. Type the following line in the Immediate window, and
then press ENTER:
?Age("11/15/67")
Note that Microsoft Access responds with the value 33
(years).
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 33
years and six months old.
By using the Date/Time tool in Control Panel, reset the date to the current date that
you noted in step 1.
For more information about date
differences, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type datediff function in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Back to the top
