date of birth

N

Nancy

Happy holidays all (especially those who are at work like
me!)

Does anyone have a routine for calculating a person's age
based on the date of birth (either 'as of current date or
a constant e.g. 1/1/04)

thanks
 
F

fredg

Nancy said:
Happy holidays all (especially those who are at work like
me!)

Does anyone have a routine for calculating a person's age
based on the date of birth (either 'as of current date or
a constant e.g. 1/1/04)

thanks
Click to expand...
Nancy,
Below is John Vinson's code to return the correct age.
The expression should be all on one line!!
Watch out for an extra > if the line gets split when sent.
There should only be one > in the expression
(> Format(Date() etc,).

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:
=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

Guest

Try This in the calculation for that field
=DateDiff("d",[Date of birth],Now())/365.25
 
L

Les

Try this in your query:DateDiff("yyyy",[birthdate],Date
()). It will give you the difference in years between the
bbirthdate and today's date.
 
J

John Spencer (MVP)

=DateDiff("d",[Date of birth],Now())/365.25
is fairly accurate, but will always be in error at some point in the year and
will return partial year information. If you need more accuracy and only need
the age in complete years, then use the formula below (all on one line).

DateDiff("yyyy",DateOfBirth,Date())
+ Cint(Format(DateOfBirth,"mmdd") > Format(Date(),"mmdd"))
 
S

Steve Schapel

Les,

This is not really a good idea, as it will always give the wrong
answer until after the person has had their birthday in the current
year.

- Steve Schapel, Microsoft Access MVP
 
N

Nancy

Thanks to all who have given suggestions. I did try the
following ... it seems to be working, but would
appreciated comments.

age: Int((Now()-[birth date])/365.25)


Thanks much :)
 
K

Ken

I tried the more accurate method mentioned below in Access
2002 but it said "function is not available in expressions
in query expression" and then listed the expression. The
less accurate one works with Now() instead of Date(). What
am I doing wrong?
-----Original Message-----
=DateDiff("d",[Date of birth],Now())/365.25
is fairly accurate, but will always be in error at some point in the year and
will return partial year information. If you need more accuracy and only need
the age in complete years, then use the formula below (all on one line).

DateDiff("yyyy",DateOfBirth,Date())
+ Cint(Format(DateOfBirth,"mmdd") > Format(Date (),"mmdd"))

[email protected] said:
Try This in the calculation for that field
=DateDiff("d",[Date of birth],Now())/365.25
Click to expand...
.
Click to expand...
 
D

Douglas J. Steele

Your references are probably messed up.

This can be caused by differences in either the location or file version of
certain files between the machine where the application was developed, and
where it's being run (or the file missing completely from the target
machine). Such differences are common when new software is installed.

On the machine(s) where it's not working, open any code module (or open the
Debug Window, using Ctrl-G, provided you haven't selected the "keep debug
window on top" option). Select Tools | References from the menu bar. Examine
all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)

For far more than you could ever want to know about this problem, check out
http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html



--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Ken said:
I tried the more accurate method mentioned below in Access
2002 but it said "function is not available in expressions
in query expression" and then listed the expression. The
less accurate one works with Now() instead of Date(). What
am I doing wrong?
-----Original Message-----
=DateDiff("d",[Date of birth],Now())/365.25
is fairly accurate, but will always be in error at some point in the year and
will return partial year information. If you need more accuracy and only need
the age in complete years, then use the formula below (all on one line).

DateDiff("yyyy",DateOfBirth,Date())
+ Cint(Format(DateOfBirth,"mmdd") > Format(Date (),"mmdd"))

[email protected] said:
Try This in the calculation for that field
=DateDiff("d",[Date of birth],Now())/365.25
Click to expand...
.
Click to expand...
Click to expand...
 
J

jwm

I've used the following successfully:

=DateDiff("yyyy",[dtmBirthDate],Date())+(DateSerial(Year(Date()),Month([dtmB
irthDate]),Day([dtmBirthDate]))>=Date())

Replace [dtmBirthDate] with the name of your field.
 
Top