date of birth

  • Thread starter Thread starter Nancy
  • Start date Start date
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
 
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.
 
Try this in your query:DateDiff("yyyy",[birthdate],Date
()). It will give you the difference in years between the
bbirthdate and today's date.
 
=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"))
 
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
 
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 :)
 
Int((Now()-[birth date])/365.25) is only an approximation, and will
occasionally return an incorrect result.

To be totally accurate, you should use the DateDiff function, and compensate
for when the person hasn't had his/her birthday yet.

DateDiff("yyyy", [Birth Date], Date()) is too literal, as it'll tell you
that it's 1 year between 31 Dec, 2003 and 1 Jan, 2004. To check whether the
person's already had his/her birthday, you need to compare the month and day
of their birth to the current month and day: if Format$([Birth Date],
"mmdd") is less than Format$(Date(), "mmdd"), then their birthday has
passed.

In other words,

DateDiff("yyyy", [Birth Date], Date()) - IIf(Format$(Date(), "mmdd") <
Format$([Birth Date], "mmdd"), 1, 0)


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Nancy said:
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 :)
-----Original Message-----
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...
Click to expand...
 
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...
 
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...
 
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.
 
Back
Top