Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    May 2002
    Posts
    36
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Caluate Age In Years And Months (97)

    I have a query that caluates the age of an individual.
    It's: DateDiff("yyyy",[DOB],Date())-IIf(Format
    ([DOB],"mmdd")>Format(Date(),"mmdd"),1,0)
    This works, but it only gives me the years. I would like
    the results to show years and months (e.g., 40 years, 8
    months). Any suggestions?

  2. #2
    New Lounger
    Join Date
    Apr 2002
    Location
    Sydney, NSW, Australia
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Caluate Age In Years And Months (97)

    you could try using the mod function & the datediff function together something like this

    DateDiff("yyyy",[DOB],Date())-IIf(Format([DOB],"mmdd")>Format(Date(),"mmdd"),1,0) mod 12

    should give you the left over months, your original expression gives you the years

    hmmmm was just thinking you might need to play around with the second part of the expression to see if you need to include the - iif part for the month calculations.

    good luck

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

    Re: Caluate Age In Years And Months (97)

    Try this expression:

    (12+Month(Date())-Month([DOB])+(Day(Date())<Day([DOB]))) Mod 12

    Note: the 12+ is there to avoid getting negative results.

  4. #4
    Lounger
    Join Date
    May 2002
    Posts
    36
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Caluate Age In Years And Months (97)

    I only got years again?

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

    Re: Caluate Age In Years And Months (97)

    See the attached Access 97 database (zipped).

    It contains a table tblBirthdays and a query qryAge that demonstrates calculating the age in years and months.

    This is the complete SQL statement of the query:

    SELECT tblBirthdays.FirstName, tblBirthdays.DOB, DateDiff("yyyy",[DOB],Date())+(Format([DOB],"mmdd")>Format(Date(),"mmdd")) AS AgeYears, (Month(Date())-Month([DOB])+12+(Day(Date())<Day([DOB]))) Mod 12 AS AgeMonths
    FROM tblBirthdays;
    Attached Files Attached Files

Posting Permissions

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