computing age

[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.

 

[Fredg]

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:
AgeateDiff("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.