Calculating Age

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 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.
Click to expand...

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.***
 
Why shouldn't this be stored in a table?

fredg said:
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.
Click to expand...

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.***
Click to expand...
 
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.
Click to expand...

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
 
Why shouldn't this be stored in a table?

fredg said:
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.
Click to expand...

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.***
Click to expand...
Click to expand...

Search google for more information:
http://www.groups.google.com

Click on Advanced search
Find Messages
With all of words Normalization Calculated

Group *Access*
 
RTimberlake said:
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.
Click to expand...

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
 
RTimberlake said:
Why shouldn't this be stored in a table?
Click to expand...

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
 
Back
Top