Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •