Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Location
    New York, New York
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Age calculation (AC2000)

    I have a database that includes the dates of births of all of the individuals. I need the individual's age as of a certain date (in this case 10/26/02). When I do a calculation for the age in a query (Age: #10/26/02# - [DateOfBirth]) I get the number of days. Short of dividing by the number of days in a year (365.25) and discarding everything after the decimal, is there a way to get the number of years between two dates? When I use the divide by method, some of the dates right around 10/26 can be off because of the way we figure an individual's age.

    Thanks,
    Karl Snyder

  2. #2
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Age calculation (AC2000)

    My appologies to the original author of this: <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    Here are some date calculations
    Put this in a column of a query or in a control.

    AgeInYears: DateDiff("yyyy",[dob],date())

    Turns out, this returns answers that are sometimes off by a year. If you try this:

    AgeInYears: Int((DateDiff("m",[dob],date())/12))

    Which seems to work better, however, it can be off by as much as a month. A
    better solution is:

    AgeInYears: Int((DateDiff("n",[dob],date())/525960))

    Which calculates the number of minutes between the dates and divides it by
    the number of minutes in the year. However, this does not calculate
    correctly on birth dates (i.e. 5/5/1976 - 5/5/2001) except on leap years.

    Now, the following works correctly based on the accepted standard of
    365.2425 days in the year:

    AgeInYears: Int((DateDiff("s",[dob],date())/31556952))

    This does not take the leap second into account (which is added every 500
    days to bring standard time up to atomic clock time), but I'm not going to
    bother, because I don't know if this effects computer clock time.
    <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Age calculation (AC2000)

    Look at <post#=187516>post 187516</post#> for more info on this.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  4. #4
    Star Lounger
    Join Date
    Sep 2002
    Location
    New York, New York
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Age calculation (AC2000)

    That's it. Thanks so much Pat.
    -Karl

  5. #5
    New Lounger
    Join Date
    May 2002
    Location
    Chicago, Illinois, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Age calculation (AC2000)

    This function returns just the age in years:

    Function intAge(Bdate As Date, refDate As Date) As Integer
    ' Returns the Age in years between 2 dates
    ' Doesn't handle negative date ranges i.e. Bdate > DateToday

    If Month(refDate) < Month(Bdate) Or (Month(refDate) = Month(Bdate) And Day(refDate) < Day(Bdate)) Then
    intAge = Year(refDate) - Year(Bdate) - 1
    Else
    intAge = Year(refDate) - Year(Bdate)
    End If

    End Function

    The function approach makes it easier to use age wherever you need it.

    Tim Mills-Groninger

Posting Permissions

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