computing age

  • Thread starter Thread starter barbara
  • Start date Start date
B

barbara

been using access2000 data base successfully on
access2002 with XP. now must determine client's ages.
first i put in just the current year and birth year and
put in a formula to do simple subtraction, which is fine
in the form screen. then i put in a field with month, so
i can tell by each month, each client's age that month
(it is necessary to know when a retired client is a
certain age). when i run the query a month at a time,
the ages all are incorrect, yet they are correct in the
form view. this is driving me crazy. i designed the
data base 3 years ago and its served for all needs until
now.
Ideally, i would like to have "current date" field
automatically entered, and have it subtract birth date
(mmddyyyy), to determine age if that's at all possible.
any help will be greatly appreciated.
 
Barbara,
Since you haven't posted the exact expression you are attempting to use,
here is one which will accurately return a person's correct age.

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

In a query:
Age:DateDiff("yyyy",[DOB],Date())-IIf(Format([DOB],"mmdd")>Format(Date(),"mm
dd"),1,0)

You could search for all records where the month of the date of birth was
the current month by setting the query criteria to:
Where Month([DOB]) = Month(Date())

Change [DOB] to the name of the actual birth date field is you are using.
 
Back
Top