Calculate record fields in table from form input

[Sean H]

I was just wondering if it was possible to have Access calculate a field in a
table upon creation of a new record. Specifically, the user is going to
enter an inspection date (mm/dd/yyyy) on a form. I would like to have two
fields in the table for quarter and fiscal year. I ran a make-table query
with Iff statements to cover the past data, but can you write one that will
continually update those fields for every new record created? Thanks

 

[John W. Vinson]

I was just wondering if it was possible to have Access calculate a field in a
table upon creation of a new record. Specifically, the user is going to
enter an inspection date (mm/dd/yyyy) on a form. I would like to have two
fields in the table for quarter and fiscal year. I ran a make-table query
with Iff statements to cover the past data, but can you write one that will
continually update those fields for every new record created? Thanks

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 just as you're now doing it -
in the control source of a Form or a Report textbox.

If you'ld like help coming up with an expression to calculate the fiscal
quarter and fiscal year, please post how your company defines those.

John W. Vinson [MVP]