how to add a formula to a field to auto calculate

[Guest]

hi,
i have a table with multiple fields... one of the fields has to be
calculated based on what is entered in one of the other fields.

there is a CPI field and Factor field; factor is calculated by the current
row CPI value divided by a value stored in a variable 'denominator'

so when a user enters the CPI value the Factor value is calculated and
populated, user can not change the value in the factor field - but can change
the values of the CPI field and the variable 'denominator'

how can i achieve this?
Thanks in advane

 

[Jeff Boyce]

By not storing the calculated value in a field!

Instead, use a query to do the calculation. The user still gets to enter
the CPI value, but the query then displays the Factor. This works quite
well in a form based on a query. You can also embed the calculation in the
form, using the AfterUpdate event of the control you use to display the CPI
value.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

hi,
i have a table with multiple fields... one of the fields has to be
calculated based on what is entered in one of the other fields.

Then this field should not exist.

there is a CPI field and Factor field; factor is calculated by the current
row CPI value divided by a value stored in a variable 'denominator'

so when a user enters the CPI value the Factor value is calculated and
populated, user can not change the value in the factor field - but can change
the values of the CPI field and the variable 'denominator'

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.

John W. Vinson [MVP]