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

Topic

New Reply

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?

Reply | Quote

Replies

I believe all you need is your same formula except use DateDiff.

Reply | Quote

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.

Reply | Quote

Datediff can be configured to return the result in whatever type of interval you want: http://www.techonthenet.com/access/functions/date/datediff.php

Reply | Quote

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()))

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Thanks a million, jmusgrove. That’s exactly what I was looking for.

Reply | Quote

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.

Reply | Quote

AGE: iif(datediff(“d”,[DOB],Date())Day([DOB]),1,0) & ” Months”, iif (Month([DOB]) >Month(Date(), datediff(“YYYY”,[DOB],Date())-1, datediff(“YYYY”,[DOB],Date()))

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

I have included a small database that seems to work.

Reply | Quote

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.
32447-CalculateAges

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

Reply | Quote

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!

Reply | Quote