date formula in table

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

Guest

Hi,
I set up a form with age calculation using following formula
=DateDiff("yyyy",[DOB],Date())+Not (Format(Date(),"y")>=Format([DOB],"y"))
It works great, the only trouble is that those values are not exported to
age column in source table. I realize that I did not specify the source
table, but I do not know how. Basically, I want age to be calculated from DOB
in the form, and then also displayed in source table (age column). Please
help me to adjust this formula. Thanks
 
First problem is that you are wanting to put a calculated value in a table.
There is almost never a reason to do that. Why taking the processing time
and the disk space for something that has to be calculated each time it is
accessed anyway?
Next, I don't understand your formula at all. What are you trying to
accomplish?
 
this formula calculates age from date of birth (another field). It works
fine. I am open to any alternatives, but I would like to have age value
somehow to be transfered from form (calculated field) to table. I will later
run queries and I will need this value.
Thanks

Klatuu said:
First problem is that you are wanting to put a calculated value in a table.
There is almost never a reason to do that. Why taking the processing time
and the disk space for something that has to be calculated each time it is
accessed anyway?
Next, I don't understand your formula at all. What are you trying to
accomplish?

alexasha said:
Hi,
I set up a form with age calculation using following formula
=DateDiff("yyyy",[DOB],Date())+Not (Format(Date(),"y")>=Format([DOB],"y"))
It works great, the only trouble is that those values are not exported to
age column in source table. I realize that I did not specify the source
table, but I do not know how. Basically, I want age to be calculated from DOB
in the form, and then also displayed in source table (age column). Please
help me to adjust this formula. Thanks
Click to expand...
Click to expand...
 
All you need to calculate age is:
=DateDiff("yyyy",[DOB],Date)
It is the other part I don't really get:
+Not (Format(Date(),"y")>=Format([DOB],"y"))

You don't need it.

You do not need to store the age in a table. Any experienced database
developer and anything you read on database design will tell you not to store
calculated values.
Even for running a query, you don't need it. You can include the
calculation in the query, for example, If you want to get the age for a
person and you have the [DOB] field, then in the field row of the column in
the query builder where you want the age:

Age: DateDiff("yyyy",[DOB],Date)

Simple as that.

alexasha said:
this formula calculates age from date of birth (another field). It works
fine. I am open to any alternatives, but I would like to have age value
somehow to be transfered from form (calculated field) to table. I will later
run queries and I will need this value.
Thanks

Klatuu said:
First problem is that you are wanting to put a calculated value in a table.
There is almost never a reason to do that. Why taking the processing time
and the disk space for something that has to be calculated each time it is
accessed anyway?
Next, I don't understand your formula at all. What are you trying to
accomplish?

alexasha said:
Hi,
I set up a form with age calculation using following formula
=DateDiff("yyyy",[DOB],Date())+Not (Format(Date(),"y")>=Format([DOB],"y"))
It works great, the only trouble is that those values are not exported to
age column in source table. I realize that I did not specify the source
table, but I do not know how. Basically, I want age to be calculated from DOB
in the form, and then also displayed in source table (age column). Please
help me to adjust this formula. Thanks
Click to expand...
Click to expand...
Click to expand...
 
=DateDiff("yyyy",[DOB],Date)
this formula does not work. I will stay with my old formula, that I found on
this forum, because it works. Thanks for your help.

Klatuu said:
All you need to calculate age is:
=DateDiff("yyyy",[DOB],Date)
It is the other part I don't really get:
+Not (Format(Date(),"y")>=Format([DOB],"y"))

You don't need it.

You do not need to store the age in a table. Any experienced database
developer and anything you read on database design will tell you not to store
calculated values.
Even for running a query, you don't need it. You can include the
calculation in the query, for example, If you want to get the age for a
person and you have the [DOB] field, then in the field row of the column in
the query builder where you want the age:

Age: DateDiff("yyyy",[DOB],Date)

Simple as that.

alexasha said:
this formula calculates age from date of birth (another field). It works
fine. I am open to any alternatives, but I would like to have age value
somehow to be transfered from form (calculated field) to table. I will later
run queries and I will need this value.
Thanks

Klatuu said:
First problem is that you are wanting to put a calculated value in a table.
There is almost never a reason to do that. Why taking the processing time
and the disk space for something that has to be calculated each time it is
accessed anyway?
Next, I don't understand your formula at all. What are you trying to
accomplish?

:

Hi,
I set up a form with age calculation using following formula
=DateDiff("yyyy",[DOB],Date())+Not (Format(Date(),"y")>=Format([DOB],"y"))
It works great, the only trouble is that those values are not exported to
age column in source table. I realize that I did not specify the source
table, but I do not know how. Basically, I want age to be calculated from DOB
in the form, and then also displayed in source table (age column). Please
help me to adjust this formula. Thanks
Click to expand...
Click to expand...
Click to expand...
 
The formula does work.

alexasha said:
=DateDiff("yyyy",[DOB],Date)
this formula does not work. I will stay with my old formula, that I found on
this forum, because it works. Thanks for your help.

Klatuu said:
All you need to calculate age is:
=DateDiff("yyyy",[DOB],Date)
It is the other part I don't really get:
+Not (Format(Date(),"y")>=Format([DOB],"y"))

You don't need it.

You do not need to store the age in a table. Any experienced database
developer and anything you read on database design will tell you not to store
calculated values.
Even for running a query, you don't need it. You can include the
calculation in the query, for example, If you want to get the age for a
person and you have the [DOB] field, then in the field row of the column in
the query builder where you want the age:

Age: DateDiff("yyyy",[DOB],Date)

Simple as that.

alexasha said:
this formula calculates age from date of birth (another field). It works
fine. I am open to any alternatives, but I would like to have age value
somehow to be transfered from form (calculated field) to table. I will later
run queries and I will need this value.
Thanks

:

First problem is that you are wanting to put a calculated value in a table.
There is almost never a reason to do that. Why taking the processing time
and the disk space for something that has to be calculated each time it is
accessed anyway?
Next, I don't understand your formula at all. What are you trying to
accomplish?

:

Hi,
I set up a form with age calculation using following formula
=DateDiff("yyyy",[DOB],Date())+Not (Format(Date(),"y")>=Format([DOB],"y"))
It works great, the only trouble is that those values are not exported to
age column in source table. I realize that I did not specify the source
table, but I do not know how. Basically, I want age to be calculated from DOB
in the form, and then also displayed in source table (age column). Please
help me to adjust this formula. Thanks
Click to expand...
Click to expand...
Click to expand...
 
Klatuu said:
The formula does work.
Click to expand...

The formula DateDiff("yyyy",[DOB],Date) Will be wrong for all the days of
the current year before the persons birthday. For example; if the DOB is
12/31/2004 it will indicate that they are one year old on 1/1/2005. You
need to add an additional expression that takes this into account.
 
Back
Top