Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2003
    Manchester, NH

    Unanswered: Age Function needed

    I am trying to calculate the age of someone on a form and a report, based on the birthdate field (ex: 06/19/1975). I need to be able to display the age in a text field on the entry form right after the person enters the birthdate on the entry form and then be able to run a report from another menu that will check for anyone over 60, based on the birthdate field and todays date. Any ideas? Datepart function seems to not be working for me. Thanks.


  2. #2
    Join Date
    Jul 2003
    This site lists the code for doing just that:
    Inspiration Through Fermentation

  3. #3
    Join Date
    Nov 2002
    try the following:

    Age = Int(DateDiff("Y", DOB, Now) / 365.25)


    Age could be either a control or variable (for my app, it was the name of a control on the form).
    DOB is the Date of Birth
    Now is a canned function in Access
    365.25 is the constant to counter leap years

    I had an interesting problem with the date() function returning,.............well nothing!!. This forced me to switch to the Now() function. The formula above seemed to work 99.999% of the time (I didn't get a wrong age once, but I'm sure there is one case where it could).

  4. #4
    Join Date
    Jun 2003
    Here is a function to calculate age. The code could be placed in any module.

    An example of use for a control's ControlSource property would be:
    =AgeInYears([DOBField], Date())

    An example of use for a calculated field in a query would be:
    AgeCalcField: AgeInYears([DOBField], Date())

    Public Function AgeInYears(ByVal pvarBirthDate As Variant, ByVal pvarCompareDate As Variant) As Long
    ' Purpose: Calc age in years as of the compare date.
    ' Accepts: pvarBirthDate date
    ' pvarCompareDate date
    ' Returns: number
    ' Remarks:
    ' Returns 0 if any needed fields blank.
    ' A simple DateDiff("yyyy",[pvarBirthDate],[pvarCompareDate]) is off
    ' by one when the month & day of the birthdate is greater than
    ' the month & day of the compare date.
    ' Access stores the date AND time in each of the date/time fields.
    ' So even though the fields may be formatted to LOOK just like
    ' date only, there also is a time stored.
    ' And the time stored is usually a constant 12:00:00 AM
    ' (effectively 0) which can be seen if you set the field to be
    ' long time format. Or even in General Date format any time but
    ' 12:00:00 AM is shown. It's usually a good idea to store a current date
    ' using Date() instead of Now() unless one really does want a time
    ' other than 12:00:00 AM to be stored.

    On Error GoTo Err_AgeInYears

    ' Exit if some fields null.
    If IsNull(pvarBirthDate) Or IsNull(pvarCompareDate) Then
    AgeInYears = 0
    Exit Function
    End If

    ' Calc age in years and subtract 1 if the birth month/day is not on or before the
    ' compare month/day.
    ' (I.e. 8/1/1995 birthdate and 9/1/1996 compare date would be age of 1
    ' and 9/1/1995 birthdate and 9/1/1996 compare date would be age of 1
    ' but 9/2/1995 birthdate and 9/1/1996 compare date would be age of 0.)
    If (Month(pvarBirthDate) < Month(pvarCompareDate)) Or (Month(pvarBirthDate) = Month(pvarCompareDate) And Day(pvarBirthDate) <= Day(pvarCompareDate)) Then
    AgeInYears = DateDiff("yyyy", pvarBirthDate, pvarCompareDate)
    AgeInYears = DateDiff("yyyy", pvarBirthDate, pvarCompareDate) - 1
    End If

    Exit Function

    MsgBox "Error " & Err & "." & Chr(13) & Chr(10) & Chr(10) & Err.Description & ".", vbExclamation
    Exit Function
    End Function
    J. Paul Schmidt, Freelance Web and Database Developer
    Access Database Sample, Web Database Sample, ASP Design Tips

Posting Permissions

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