Calculated field not working

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I have a field on my form called "Total Mail Destroyed" It is calculated by
the number of "Total Mail Received" Minus "Total Mail Worked" which are also
fields on the form. In the Control source of the TMD field, I have
=[MREC]-[TMW]. It works fine on the form but does not show on the table or
report. In code in the Before Update property of the TMD field I have Me.TMD
=[MREC]-[TMW]. Can someone please explain what I did worng. Also, please
simplify response as you can see I am a novice with Access.

Thank you!!
 
Doug,

Storing a calculated field is neither desirable nor necessary. Doing so
requires code, risks the data being incorrect if the fields upon which it
depends are changed outside the context of your form, or outside the context
of the conditions you considered when writing it. It is also much faster to
calculate it on the fly than to look it up from disk.

For example, if you had length, width, and height dimension fields for an
entity, you'd never attempt to store its xy-plane area or volume--simply use
a calculated field in a query to return the always correct answer on the fly
for use in reports.

Hope that helps.
Sprinks
 
Hello,

I have a field on my form called "Total Mail Destroyed"
Click to expand...

Forms don't have "fields" - tables do. Forms have Controls, which
might or might not be bound to a table Field. It's a fine distinction
but an important one, and I believe it might be the cause of your
problem!
It is calculated by
the number of "Total Mail Received" Minus "Total Mail Worked" which are also
fields on the form. In the Control source of the TMD field, I have
=[MREC]-[TMW]. It works fine on the form but does not show on the table or
report.
Click to expand...

Correct. If you're calculating it on the Form, it is on the Form - and
NOT in the Table. But it should not *BE* in the table!

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.

Just put a textbox on the Report with the same control source, and it
will recalculate the difference there. No benefit will come from
storing this value in your Table.

John W. Vinson[MVP]
 
You are correct with one exception. For the report, it should be calculated
on the report. It will run much faster that way. Do it just like you are
doing it on the form. Create an unbound text box on the form and do the
calculation there.

Sprinks said:
Doug,

Storing a calculated field is neither desirable nor necessary. Doing so
requires code, risks the data being incorrect if the fields upon which it
depends are changed outside the context of your form, or outside the context
of the conditions you considered when writing it. It is also much faster to
calculate it on the fly than to look it up from disk.

For example, if you had length, width, and height dimension fields for an
entity, you'd never attempt to store its xy-plane area or volume--simply use
a calculated field in a query to return the always correct answer on the fly
for use in reports.

Hope that helps.
Sprinks


Doug_C said:
Hello,

I have a field on my form called "Total Mail Destroyed" It is calculated by
the number of "Total Mail Received" Minus "Total Mail Worked" which are also
fields on the form. In the Control source of the TMD field, I have
=[MREC]-[TMW]. It works fine on the form but does not show on the table or
report. In code in the Before Update property of the TMD field I have Me.TMD
=[MREC]-[TMW]. Can someone please explain what I did worng. Also, please
simplify response as you can see I am a novice with Access.

Thank you!!
Click to expand...
Click to expand...
 
Hi Sprinks,

Thank you for your quick response! Ok, that sounds fine. So if I use the
query to calculate the two fields and show the total in my report, what would
the formula or code be? When I enter =[MREC]-[TMW] in the TMD field on the
query, when I pull the report it is asking me for TMW. I'm sure I am not
doing something right.

Thanks!

Sprinks said:
Doug,

Storing a calculated field is neither desirable nor necessary. Doing so
requires code, risks the data being incorrect if the fields upon which it
depends are changed outside the context of your form, or outside the context
of the conditions you considered when writing it. It is also much faster to
calculate it on the fly than to look it up from disk.

For example, if you had length, width, and height dimension fields for an
entity, you'd never attempt to store its xy-plane area or volume--simply use
a calculated field in a query to return the always correct answer on the fly
for use in reports.

Hope that helps.
Sprinks


Doug_C said:
Hello,

I have a field on my form called "Total Mail Destroyed" It is calculated by
the number of "Total Mail Received" Minus "Total Mail Worked" which are also
fields on the form. In the Control source of the TMD field, I have
=[MREC]-[TMW]. It works fine on the form but does not show on the table or
report. In code in the Before Update property of the TMD field I have Me.TMD
=[MREC]-[TMW]. Can someone please explain what I did worng. Also, please
simplify response as you can see I am a novice with Access.

Thank you!!
Click to expand...
Click to expand...
 
Back
Top