doing math in the table

[Jacques]

Is it possible to have a field recieve calculated values from two other
fields?

I tried it with a form (which does the math) but it doesn't actually add the
data to the field.

 

[Ken Snell [MVP]]

No. And you don't do that in ACCESS anyway. If the value can be calculated
from two other fields, normally you do that in a query when you display the
data.

There are some times when you do store calcualated values, but when you must
do that, use a query or a form to calculate the value and to put it into the
table.

 

[John Vinson]

Is it possible to have a field recieve calculated values from two other
fields?

I tried it with a form (which does the math) but it doesn't actually add the
data to the field.

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]

 

[Tom]

Yes. Create a macro to set the value (Setvalue) of the field in the table
rather than a calculated contron on the form. If you want the form to dispaly
the value, set the ":control Source"of the form field to display the value in
the table.

 

[Jack MacDonald]

It is generally recommended not to store calculated values in the
tables. Instead, calculate the values in a query.

Yes. Create a macro to set the value (Setvalue) of the field in the table
rather than a calculated contron on the form. If you want the form to dispaly
the value, set the ":control Source"of the form field to display the value in
the table.

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

 

[Jacques]

Thanks everyone,
I'm going to do it with a form. I'm making a time sheet form for
multiple users and wanted two things primarily:

1) When a user logs on, they can only see their timesheets in the table (I
can't get my queries to allow data entry when the query filters by the
user - but filtering based upon who logged in does work). This is one of
the reasons I wanted to put the data in the table, because basing the form
on the query instead of the tables isn't allowing for data entry (and no,
view totals is not selected).

2) I want the users to be able to see the calculated TotalTime from the
TimeIn and TimeOut fields, and I want the TimeIn and TimeOut to round off to
the nearest quarter hour.