Calculate Text Field and Save in Table

  • Thread starter Thread starter Matt Amman via AccessMonster.com
  • Start date Start date
M

Matt Amman via AccessMonster.com

I have a text field that I need to calculate the difference of two other
fields. Here is my code:
In the source code field of "diff in hours" I coded

=[hours]-[paid_hours]

It calculates okay, but it doesn't save the value in the "diff in hours"
field in the table.

How do I calculate and have the total show up in the table?

Thanks,
Matt
 
Matt said:
I have a text field that I need to calculate the difference of two other
fields. Here is my code:
In the source code field of "diff in hours" I coded

=[hours]-[paid_hours]

It calculates okay, but it doesn't save the value in the "diff in hours"
field in the table.

How do I calculate and have the total show up in the table?
Click to expand...


Making Access do that is a little tricky (for a beginner).
A good reason why it's tricky is because you are not
supposed to do that.

A fundamental rule of relational databases (not just Access)
is that you should not save values that can be calculated
from other saved values (especially for a simple calculation
like that). Just recalculate the derived value whenever you
need to see it in a form or report.
 
I have a text field that I need to calculate the difference of two other
fields. Here is my code:
In the source code field of "diff in hours" I coded

=[hours]-[paid_hours]
Click to expand...
It calculates okay, but it doesn't save the value in the "diff in hours"
field in the table.
Click to expand...

It doesn't, and it shouldn't. The field [diff in hours] should NOT
EXIST.

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