Calculate age months, years

[mulberry13]

I have a query that I need to show me ages in months & years from birth. This is what I currently have in the query. How do I add the "months" so it will also calculate that?

Current Age: Year(Now())-Year([Date of Birth])

 

[Montez659]

Just like you already have:

Month(Now())-Month([Date of Birth])

and then one for days:

Day(Now())-Day([Date of Birth])

And then you can concatenate them together how you would like.

You mentioned query though, so not sure what you mean by that. If you are looking for a specific span of time, then you would need an iif statement in the query. If this is just to view in a report, the above should work for you.

 

[mulberry13]

Right now it is just to show me age of any active client

 

[Montez659]

You would probably want that to show in a report and not in a query. A query will help you filter the data and a report will help you display. Therefore, the above code would best be viewed in a report, where you queried for active clients.

 

[mulberry13]

I'm sorry I did actually create a report that is pulling the info from this query. Is that right?

 

[mulberry13]

Ok spoke too soon! I want all of the info together so the report will show "years, month, days) ages. How can I add the "month/days" right into this query? I'm not sure where the "month" day" would go in this query.

Current Age: Year(Now())-Year([Date of Birth])

 

[Phildaburn]

The method shown above will only calculate a difference in the month number, not in the total number of months a person has been alive. So using the calculation above, someone born in March of 1960 compared to a date of March of 2011 will return a 0. Is that what you're after? If not, you'll need to use the DateDiff function.

DateDiff("m",#3/15/1960#,now()) returns 612 where "now" = 3/25/2011.
Month(now())-Month(#3/15/1960#) returns 0 where "now" = 3/25/2011.

Phil...

P.S. Just substitute [Date of Birth] where I have used #3/15/1960#.

 

[Montez659]

Good catch Phil, my brain hasn't quite woke up this morning.

Mulberry - again, not sure why you want this in a query. You would want this formula to be a part of the report where the calculation is done in the report itself and associated with the record. You would take Phil's code or mine and set the control source of an unbound textbox to his equation and that would return what you are looking for.

 

[mulberry13]

So, this is all added to that one line in the query or are they in 3 different columns in the query? Sorry to be a pain!

 

[Phildaburn]

SELECT [DoB Test].[Date of Birth], Date() AS Today, IIf(Month([date of birth])>Month(Date()),DateDiff("yyyy",[date of birth],Date())-1,DateDiff("yyyy",[date of birth],Date())) AS Years, IIf(Month([date of birth])>Month(Date()),Month(Date())+12-Month([date of birth]),Month(Date())-Month([date of birth])) AS Months
FROM [DoB Test]
WITH OWNERACCESS OPTION;

I set up a table "DoB Test" and populated a few dates. The query above will give you Years difference and Months difference. Couldn't get Days quite right, but here are the results:

<TABLE border=1 cellSpacing=0 bgColor=#ffffff><CAPTION>Query1</CAPTION><THEAD><TR><TH bgColor=#c0c0c0 borderColor=#000000>Date of Birth</TH><TH bgColor=#c0c0c0 borderColor=#000000>Today</TH><TH bgColor=#c0c0c0 borderColor=#000000>Years</TH><TH bgColor=#c0c0c0 borderColor=#000000>Months</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#d0d7e5 align=right>1/15/1960</TD><TD borderColor=#d0d7e5 align=right>3/25/2011</TD><TD borderColor=#d0d7e5 align=right>51</TD><TD borderColor=#d0d7e5 align=right>2</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>2/18/1961</TD><TD borderColor=#d0d7e5 align=right>3/25/2011</TD><TD borderColor=#d0d7e5 align=right>50</TD><TD borderColor=#d0d7e5 align=right>1</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>3/25/1962</TD><TD borderColor=#d0d7e5 align=right>3/25/2011</TD><TD borderColor=#d0d7e5 align=right>49</TD><TD borderColor=#d0d7e5 align=right>0</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>4/8/1963</TD><TD borderColor=#d0d7e5 align=right>3/25/2011</TD><TD borderColor=#d0d7e5 align=right>47</TD><TD borderColor=#d0d7e5 align=right>11</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>5/1/1964</TD><TD borderColor=#d0d7e5 align=right>3/25/2011</TD><TD borderColor=#d0d7e5 align=right>46</TD><TD borderColor=#d0d7e5 align=right>10</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>6/30/1965</TD><TD borderColor=#d0d7e5 align=right>3/25/2011</TD><TD borderColor=#d0d7e5 align=right>45</TD><TD borderColor=#d0d7e5 align=right>9</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>7/28/1966</TD><TD borderColor=#d0d7e5 align=right>3/25/2011</TD><TD borderColor=#d0d7e5 align=right>44</TD><TD borderColor=#d0d7e5 align=right>8</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

Hope this helps.

Phil...