Results 1 to 5 of 5
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access (XP Professional)

    Access stores dates in the form of the number of days since 31 December 1899. So if you subtract two dates, you get the number of days between them.

    You can calculate age using a little custom function. Copy the following code to a standard module:

    Function Age(Date1, Date2) As Integer
    ' Returns the Age in years between 2 dates
    ' Doesn't handle negative date ranges i.e. Date1 > Date2
    Age = Year(Date2) - Year(Date1)
    If Month(Date2) < Month(Date1) Or (Month(Date2) = Month(Date1) And Day(Date2) < Day(Date1)) Then
    Age = Age - 1
    End If
    End Function

    Use this function in a query to define a calculated field:

    TheAge: Age([Date of birth], [Start of Year])

    or as control source of a text box on a form or report:

    =Age([Date of birth], [Start of Year])

  2. #2
    Star Lounger
    Join Date
    Dec 2001
    Location
    Birmingham, Alabama USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access (XP Professional)

    Try this user defined function:
    <pre>Public Function fGetAgeAsOf(dtDOB As Variant, dtAsOf As Variant) As Integer
    Dim dtBDay As Date
    If Not IsDate(dtDOB) Or Not IsDate(dtAsOf) Then Exit Function
    dtBDay = DateSerial(Year(dtAsOf), Month(dtDOB), Day(dtDOB))
    fGetAgeAsOf = DateDiff("yyyy", dtDOB, dtAsOf) + (dtBDay > dtAsOf)
    End Function</pre>


    HTH
    RDH
    Ricky Hicks
    Microsoft MVP
    Birmingham, Alabama USA

  3. #3
    Star Lounger
    Join Date
    Jun 2003
    Location
    Hadfield, Derbyshire, England
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculate age (Access XP Professional)

    <P ID="edit" class=small>(Edited by HansV on 02-Sep-03 12:51. Changed subject to something more descriptive than just 'Access'.)</P>Hello ,

    Is there a way of calculating an age of something in a query i am trying to obtain an age in years of someone, I have tried an expression of
    Age =[ Start of Year]-[ Date of Birth] which comes out with a very high figure ie 4569.
    My query consists of 3 Columns Name Start of year(ie 01 January 2003) Date of Birth (ie 12 July 1989), i need an expression to automatialy calculate the Age in Years. For every name the Start of year date will be the same or i might want to change it to the Current Date. Any help would be appreciated

  4. #4
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Access (XP Professional)

    uh oh. I have been using the following to calculate age. It seemed to work ok, but now, based on these kewl funtions presented here, I am concerned it may be incorrect.

    'Age in years is Current date (dtmToday) less Birth date (dtmBdate)
    Int((dtmToday-dtmBdate)/365.25)

    In a query it looks like:
    Age: Int((dtmToday-dtmBdate)/365.25)

    Is this oversimplified?

    Thanks,

    Ken

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Access (XP Professional)

    I think this formula may fail for certain specific situations. I believe those situations are when today's date is on or just before the birthdate (month/day, that is), and may also be dependent on if date is before March 1. The result isn't catastrophic, a person who will be 21 tomorrow may calculate as 21 today.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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