Calculating ages

  • Thread starter Thread starter Heather
  • Start date Start date
H

Heather

G'day.

Can anyone tell me how to do a calculation in a query
which will return a rounded number for an age?

I've got a DOB field - dd/mm/yyyy format - and in a query
I've set up I can subtract the DOB from Date() to get a
total number of days difference. I then divide by 365 to
get the years, but this result in a lot of following
decimal places.

In the properties, I tried setting it to General Number, 0
decimal places, but this doesn't work. The result remains
the same.

How do I get the query to calculate on the Year component
of the DOB and Date() only, OR to return a whole figure on
the entire date?

A simple thing to do, one would think, but "Help" is
anything but... Can't find any relevant examples and
don't have time to learn programming!

Have a good day,

Heather
Melbourne, Victoria, Australia
 
Heather,
In a query:
Age: DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") >
Format(Date(), "mmdd"), 1, 0)

Directly as the control source of an unbound control in a form or a report:
=DateDiff("yyyy",[DOB],Date())-IIf(Format([DOB],"mmdd")>Format(Date(),
"mmdd"),1,0)

You do know, I hope, that this Age computation should NOT be stored in
any table.
Just compute it and display it on a form or report, as needed.
 
G'day Fred,

Thanks for the suggestion. As the site didn't display my
posting for over a day, I had time to tinker and explore,
and came up with an alternative solution, which works
perfectly and is a bit easier to type in:-

=(DatePart("yyyy",Date())-(DatePart("yyyy",[DOB])))

One thing I like about Windows software is there are
always at least three different ways to achieve the same
outcome.

I'll tinker with your suggestion as well, and see under
what circumstances it may work better than my solution.

Again, thanks for your help,

Cheers,

Heather. :-)
-----Original Message-----
Heather,
In a query:
Age: DateDiff("yyyy", [DOB], Date()) - IIF(Format ([DOB], "mmdd") >
Format(Date(), "mmdd"), 1, 0)

Directly as the control source of an unbound control in a form or a report:
=DateDiff("yyyy",[DOB],Date())-IIf(Format([DOB],"mmdd")
Format(Date(),
"mmdd"),1,0)

You do know, I hope, that this Age computation should NOT be stored in
any table.
Just compute it and display it on a form or report, as needed.

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


Heather said:
G'day.

Can anyone tell me how to do a calculation in a query
which will return a rounded number for an age?

I've got a DOB field - dd/mm/yyyy format - and in a query
I've set up I can subtract the DOB from Date() to get a
total number of days difference. I then divide by 365 to
get the years, but this result in a lot of following
decimal places.

In the properties, I tried setting it to General Number, 0
decimal places, but this doesn't work. The result remains
the same.

How do I get the query to calculate on the Year component
of the DOB and Date() only, OR to return a whole figure on
the entire date?

A simple thing to do, one would think, but "Help" is
anything but... Can't find any relevant examples and
don't have time to learn programming!

Have a good day,

Heather
Melbourne, Victoria, Australia
Click to expand...


.
Click to expand...
 
=(DatePart("yyyy",Date())-(DatePart("yyyy",[DOB])))
Click to expand...

Try this with:

DatePart("yyyy", #1/1/2003#) - DatePart("yyyy", #12/31/2002#)

You'll see that it will give an incorrect age: a year old for a
day-old baby!

This expression will return exactly the same age as a simple
DateDiff("yyyy", [DOB], Date()) - and they're both wrong if the
person's birthday anniversary has not yet arrived this year. To
correct for this one-year error subtract 1 if that is the case:

DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") >
Format(Date(), "mmdd"), 1, 0)
 
Back
Top