Results 1 to 15 of 15
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    320
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Calculate an age, adding # of months if child is not yet 1 year old

    Hello, all. I am working on a membership database for our church and have run in to a problem. I need a formula that calculates an age, and I have found that information and can make it work, but how can I add "number of months" if a child is not yet 1 year old? I have all the information currently in an Excel spreadsheet and it works there like a charm. This is the formula used in Excel. =IF(DATEDIF(J130,TODAY(),"y")>0,DATEDIF(J130,TODAY (),"y"),DATEDIF(J130,TODAY(),"m")&" Months") For instance, if a child is born on 12/21/11, it would show "10 months" for that child's age. I have attached an Excel spreadsheet that shows how it is done there and how I would like it to be in Access. Does anyone have an idea of how I can do this in Access?
    Attached Files Attached Files

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,537
    Thanks
    0
    Thanked 23 Times in 23 Posts
    I believe all you need is your same formula except use DateDiff.

  3. #3
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    320
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I'm sorry, that didn't work. I tried IIf(datediff.....) and I think it was that one that calculated years OK, but I never could get it to show "months". I was on a deadline, so I finally exported it to Word and edited it in Word where I needed to add 'months'. Thank you for at least looking at it. The crunch is off, but I'd still be interested in a solution, if someone has one.

  4. #4
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,308
    Thanks
    130
    Thanked 1,159 Times in 1,067 Posts
    Datediff can be configured to return the result in whatever type of interval you want: http://www.techonthenet.com/access/f...e/datediff.php

  5. #5
    New Lounger
    Join Date
    Nov 2012
    Posts
    4
    Thanks
    0
    Thanked 1 Time in 1 Post
    The datediff paramaters are in a different order than the datedif example from excel. Try this:

    AGE: iif(datediff("YYYY",[DOB],now())=0,datediff("m",[DOB],now()) & " Months",datediff("YYYY",[DOB],now()))

  6. #6
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    320
    Thanks
    1
    Thanked 0 Times in 0 Posts
    OK. That 'almost works'. What it does is gives me the months if a child is under 1 year, but not exact. It rounds up. For instance, a child whose birthday is 12/21/11, it shows as 1 year, and a child with a birthday of 8/25/12 it shows as 3 months.

  7. #7
    New Lounger
    Join Date
    Nov 2012
    Posts
    4
    Thanks
    0
    Thanked 1 Time in 1 Post
    so, you are only interested in "whole" months, then? The Datediff function simply subtracts the month numbers and returns the result, adding 12 for each additional year. If you want to round down to whole months (i.e. 364 days old = 11 months), then you are going to have to adjust the results to compensate for the difference in days and subtract one month. Try this

    AGE: iif(datediff("d",[DOB],now())<365,datediff("m",[DOB],now()) - iif(datepart("d",now())-datepart("d",[DOB])<0,1,0) & " Months",datediff("YYYY",[DOB],now()))

    notice that I trigger the "months" result based on 365 days old rather than "1 year", since datediff will return "1" year for the entire birth month. If the day of the month is greater than the day of the dob, I subtract 1 month to remove the incomplete month from the result. This sounds to me like what you are after.

  8. The Following User Says Thank You to jmusgrove For This Useful Post:

    79schultz (2012-11-12)

  9. #8
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    320
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks a million, jmusgrove. That's exactly what I was looking for.

  10. #9
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    320
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Ooops. I guess I have to back up a little. With that formula, it finds the correct months, but now if a person's birthday is between now and the end of the year, it adds 1 to their age.

  11. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,537
    Thanks
    0
    Thanked 23 Times in 23 Posts
    AGE: iif(datediff("d",[DOB],Date())<365, datediff("m",[DOB],Date()) - iif(Day(Date())>Day([DOB]),1,0) & " Months", iif (Month([DOB]) >Month(Date(), datediff("YYYY",[DOB],Date())-1, datediff("YYYY",[DOB],Date()))

  12. #11
    Lounger
    Join Date
    Feb 2011
    Posts
    43
    Thanks
    0
    Thanked 6 Times in 6 Posts
    There is a problem though catering for leap years with 366 days.

    Your a better man than me with complex IIf statements, but the first part would be
    IIf(DateSerial(Year(pDOB) + 1, Month(pDOB), Day(pDOB)) <= Date,.....

    But there seems to be issues with the rest because I get the results below for a birth date of 16 November 2011 (NB UK date format). I'd be using a function.

    Cronk

    17/11/2011 1
    18/11/2011 12 Months
    19/11/2011 12 Months
    ....
    29/11/2011 12 Months
    30/11/2011 12 Months
    1/12/2011 10 Months
    2/12/2011 10 Months
    ...
    15/12/2011 10 Months
    16/12/2011 11 Months
    17/12/2011 11 Months
    18/12/2011 11 Months
    ....
    30/12/2011 11 Months
    31/12/2011 11 Months
    1/01/2012 9 Months
    2/01/2012 9 Months
    3/01/2012 9 Months

  13. #12
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,537
    Thanks
    0
    Thanked 23 Times in 23 Posts
    Sorry that was air code, I will look into it later. It seems like my carriage return won't work in here, anyone got any ideas?

  14. #13
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,537
    Thanks
    0
    Thanked 23 Times in 23 Posts
    I have included a small database that seems to work.
    Attached Files Attached Files

  15. #14
    Star Lounger tgw7078's Avatar
    Join Date
    Jul 2010
    Location
    Seattle, WA., USA
    Posts
    83
    Thanks
    1
    Thanked 10 Times in 10 Posts

    Calling a custom function to calculate ages

    You might want to try a custom function, written by Graham Seach and Doug Steele:

    A More Complete DateDiff Function
    http://www.accessmvp.com/djsteele/Diff2Dates.html

    I have attached a sample database, in Access 2000 file format, that includes two queries that call the "Diff2Dates" function.
    CalculateAges.zip

    Note: If you are running Access 2007 or 2010, make sure to trust the folder first, since this sample includes Visual Basic for Applications (VBA) code:

    Dealing with the Trust Center
    http://www.accessmvp.com/TWickerath/articles/trust.htm

    The first query, "qryCalculateAges", includes the original data from your spreadsheet, along with age calculations for November 5 (the date you started this thread), and the current system date on your computer as today's date. The two calculated ages are presented in "ymd" (Year-Month-Day) format. The data is sorted by birth month and birth day (1/25, 2/15, 3/1, etc.).

    In the second query, ""qryCalculateAgesWithNestedIIFStatements, I used a rather ugly series of nested IIF statements to show age in:
    • Years only if they are 16 or older
    • Years and months, if they are between 13 months to 15 years of age and
    • Years, months and days, if they are 12 months or younger
    Last edited by tgw7078; 2012-11-22 at 05:14.
    Tom Wickerath
    Microsoft Access MVP
    4/1/2006 - 3/31/2012

  16. #15
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    320
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I thank all of you for your help, I have been too busy at work to get around to this and am leaving on a business trip today. I'll try to get back to this (I hate to leave jobs unfinished) when I return. Thanks, everyone!

Posting Permissions

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