Re: Calculating Age
fredg
> I am trying to calculate the age of our boys when they enter our program.
> This neds to be done on a form and stored in a table.
> The table is tblMain
> The feilds are "Date of Birth" "Date of Entrance" and "Age at Entrance"
> I have read the previous posts and can't seem to make sense of them.
> Please let me know where I need to enter the code and what needs to be
> entered. I have tried the DateDiff function but I can't figure it out.
Here is an expression that will accurately calculate a persons age.
Watch out for word wrap.
In a query:
Age: DateDiff("yyyy", [DOB], [Date of Entrance]) - IIF(Format([DOB],
"mmdd") >
Format([Date of Entrance], "mmdd"), 1, 0)
Directly as the control source of an unbound control on a form or in a
report:
=DateDiff("yyyy",[DOB],[Date of Entrance])-
IIf(Format([DOB],"mmdd")>Format([Date of Entrance],
"mmdd"),1,0)
***This Age computation should NOT be stored in any table.
Whenever you need the age, just compute it and display it on a form or
report, as needed.***
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
RTimberlake
Mike Labosh
> This neds to be done on a form and stored in a table.
> The table is tblMain
> The feilds are "Date of Birth" "Date of Entrance" and "Age at Entrance"
> I have read the previous posts and can't seem to make sense of them.
> Please let me know where I need to enter the code and what needs to be
> entered. I have tried the DateDiff function but I can't figure it out.
tblMain should have a column for [Date of Birth] and one for [Date of
Entrance]. These values can be entered by the users into bound textboxes,
calendar control, date combo box, etc.
[Age at Entrance] should not get stored anywhere in the database, because it
is a calculated value. Nor should it be updatable by the users.
For the [Age at Entrance] text box on your form, goto the "ControlSource"
property and enter this expression:
= DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])
Where interval should be "yyyy" to indicate you're counting years
Where date1 is the Date of Birth
Where date2 is the Date of Entrance
The last two arguments are optional.
I would recommend using this expression as your ControlSource for your [Age
at Entrance] value:
= DateDiff("yyyy", [Date of Birth], [Date at Entrance])
As long as the form contains bound controls for [Date of Birth] and [Date at
Entrance], then this will automatically display the Age at Entrance as the
result of the calculation.
You can goto Microsoft Access Help and search on the word, DateDiff
--
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane
fredg
Search google for more information:
http://www.groups.google.com
Click on Advanced search
Find Messages
With all of words Normalization Calculated
Group *Access*
James A. Fortune
> I am trying to calculate the age of our boys when they enter our program.
> This neds to be done on a form and stored in a table.
> The table is tblMain
> The feilds are "Date of Birth" "Date of Entrance" and "Age at Entrance"
> I have read the previous posts and can't seem to make sense of them.
> Please let me know where I need to enter the code and what needs to be
> entered. I have tried the DateDiff function but I can't figure it out.
>
I use the following expression for Age:
Int(Format([CurrentDate],"yyyy.mmdd") - Format([BirthDate],"yyyy.mmdd"))
It would be implemented like:
SELECT Int(Format([Date of Entrance],"yyyy.mmdd") - Format([Date of
Birth],"yyyy.mmdd")) AS [Age at Entrance] FROM tblMain;
I agree with others that this value doesn't need to be stored in the table.
James A. Fortune
John W. Vinson/MVP
"RTimberlake" <RTimb...@discussions.microsoft.com> wrote in message
news:35FAC219-BFB4-44F6...@microsoft.com...
> Why shouldn't this be stored in a table?
Because if it's right today... it will be wrong anytime from tomorrow until
a year from today. Storing data that you KNOW will be wrong at some point is
just a pointless waste of space, given that it is not *necessary* to store
it in order to display it, use it, sort by it, search for it, etc. etc.
John W. Vinson/MVP