calculate table field value

[Portia]

Is it possible to calulate a field in a table? I want to have [age] as a
field in my table, and I want it to be automatically calculated from DOB (a
field in the table) and the current date. (I know I can do this in a form or
query, but I want the field in the table).

 

[Arvin Meyer [MVP]]

Not only is it not possible, it's not proper database design. Your request
is one of the main reasons that it isn't advisable. What happens next year?
Does it recalculate every day?

Instead, use a query or form to calculate, which I realize is not what you
want, but is the most desirable. Keep in mind that tables really should
never be used to display or enter data. Their purpose is to store it.

 

[fredg]

Is it possible to calulate a field in a table? I want to have [age] as a
field in my table, and I want it to be automatically calculated from DOB (a
field in the table) and the current date. (I know I can do this in a form or
query, but I want the field in the table).

Why would you wish to 'store' data that is sure to be incorrect at
least once a year?

Store the date of birth in your table, then whenever you need tha
actual age, calculate it.

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)

Where [DOB] is the birthdate field.

 

[John W. Vinson]

but I want the field in the table

Why?

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or in the control source of a Form or a Report
textbox.