calculating children's ages

[Guest]

I have seen the responses to the calculating ages questions and see that Tom
Lake's response to Todd does work. However, I need to produce the child's
age in years and months. The db is for a child care center where the more
exact age is needed. I do not want the age in months only because the user
then must manually divide to figure out the age of a child -- 49 months is 4
yrs/1 mos. Nor do I want to use a decimal approach because there are 12
months in a year. Must I use 2 fields and calculate separately? If so what
is the formula to show only the remainder months? I'd rather have one field
if possible.

 

[Steve Schapel]

Nancy,

If you are doing it in a query, make a calculated field like this:
Age: (DateDiff("m",[DOB],Date())+(Day([DOB])>Day(Date())))\12 & " yrs
" & (DateDiff("m",[DOB],Date())+(Day([DOB])>Day(Date()))) Mod 12 & " mos"

Same thing if you want it in the control source of a textbox on your
form or report, just put a = in front of the expression.

 

[Guest]

Many thanks -- will try it!
--
nhb -- nc

Nancy,

If you are doing it in a query, make a calculated field like this:
Age: (DateDiff("m",[DOB],Date())+(Day([DOB])>Day(Date())))\12 & " yrs
" & (DateDiff("m",[DOB],Date())+(Day([DOB])>Day(Date()))) Mod 12 & " mos"

Same thing if you want it in the control source of a textbox on your
form or report, just put a = in front of the expression.

--
Steve Schapel, Microsoft Access MVP

I have seen the responses to the calculating ages questions and see that Tom
Lake's response to Todd does work. However, I need to produce the child's
age in years and months. The db is for a child care center where the more
exact age is needed. I do not want the age in months only because the user
then must manually divide to figure out the age of a child -- 49 months is 4
yrs/1 mos. Nor do I want to use a decimal approach because there are 12
months in a year. Must I use 2 fields and calculate separately? If so what
is the formula to show only the remainder months? I'd rather have one field
if possible.

 

[Guest]

Worked like a charm!! Many thanks.
--
nhb -- nc

Nancy,

If you are doing it in a query, make a calculated field like this:
Age: (DateDiff("m",[DOB],Date())+(Day([DOB])>Day(Date())))\12 & " yrs
" & (DateDiff("m",[DOB],Date())+(Day([DOB])>Day(Date()))) Mod 12 & " mos"

Same thing if you want it in the control source of a textbox on your
form or report, just put a = in front of the expression.

--
Steve Schapel, Microsoft Access MVP

I have seen the responses to the calculating ages questions and see that Tom
Lake's response to Todd does work. However, I need to produce the child's
age in years and months. The db is for a child care center where the more
exact age is needed. I do not want the age in months only because the user
then must manually divide to figure out the age of a child -- 49 months is 4
yrs/1 mos. Nor do I want to use a decimal approach because there are 12
months in a year. Must I use 2 fields and calculate separately? If so what
is the formula to show only the remainder months? I'd rather have one field
if possible.