Results 1 to 8 of 8
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Work out age in YRS and Mnths (Access 2000>)

    I would like to display the age of a person in YRS and MNTHS within a calculated field in a query. The field value must show eg. 24YR 8MNTH. How do you do this!
    TIA
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Work out age in YRS and Mnths (Access 2000>)

    See for example <post:=461,962>post 461,962</post:> and <post:=237,840>post 237,840</post:>. If you search this forum for age years months you'll find more.

  3. #3
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Work out age in YRS and Mnths (Access 2000>)

    Try this expression (where <!t>[dob]<!/t> is your date of birth field):

    Int((Date()-[dob])/365.25) & "YR " & Int(((Date()-[dob]) Mod 365.25)/30) & "MNTH"
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Work out age in YRS and Mnths (Access 2000>)

    Tx for replies, its great. I feel quite silly, as I was so frustrated with this that I reverted to the forum and immediately posted a Q. It didn't even occur to me to search until 10min after the post! When I did, I found 4 threads dealing with this!
    I apologise...I reacted to the situation without thinking!
    Regards,
    Rudi

  5. #5
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Work out age in YRS and Mnths (Access 2000>)

    No worries. In hindsight, scrap my earlier reply as the following is far more elegant:

    Format(Date()-[dob],"yy""YR ""m""MTH""")
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Work out age in YRS and Mnths (Access 2000>)

    Tx Waggers
    Regards,
    Rudi

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Work out age in YRS and Mnths (Access 2000>)

    That will return the correct age in most, but unfortunately not all situations. For example, if DOB = 23-November-2005 and Date() = 24-November-2005, the result is 99YR 12MTH. I'm afraid you do need one of the more complicated formulas.

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Work out age in YRS and Mnths (Access 2000>)

    This is elegant, but it does not give the right answers.

    Today (24 Nov 2005) if I put in birth date of 22 Nov 1951 it gives an age of 54 yrs and 1 month rather than 54 Yrs 0 months.

    If I put in a birth date of 25 Nov 1951 it gives 53 yrs and 12 months.
    Regards
    John



Posting Permissions

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