1. ## Calculate Age (2000 all updates)

Given a person's Date of Birth, [DOB] field in table, I want to produce a report which gives the person's age in Years and Months...with two additional wrinkles. If the person is under 1 year old , I want to be able to show the age in Months, Weeks and Days. If the person is from 1 to 3 years of age, I want to be able to show the age in Years, Months, and Weeks.

I obtained the following code from the Microsoft website. How do I expand the code to include Weeks and Days? I think then I could work out the other wrinkles which are dependent upon the under 1 and under 3 years of age.

Tom

' 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, Date)
If Date < DateSerial(Year(Date), 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, Date))
If (DatePart("d", StartDate) > DatePart("d", Date)) Then
tAge = tAge - 1
End If
If tAge < 0 Then
tAge = tAge + 1
End If

AgeMonths = CInt(tAge Mod 12)

End Function

2. ## Re: Calculate Age (2000 all updates)

See the attached text file. The code in the text file can be copied into a standard module. The function to use is ShowAge.

In a query:

Age: ShowAge([DOB])

In the Control Source of a text box on a form or report:

=ShowAge([DOB])

3. ## Re: Calculate Age (2000 all updates)

Hans
Thanks so much!

That does the trick !!! Precisely what I wanted.

You're the best.
Tom

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•