Tags: access, bythe, calculated, database, destroyed, field, form, mail, microsoft, minus, mysql, number, oracle, received, sql, total, working

Calculated field not working

On Database » Microsoft Access

5,833 words with 4 Comments; publish: Sat, 07 Jun 2008 22:46:00 GMT; (25077.15, « »)

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!!

All Comments

Leave a comment...

  • 4 Comments
    • 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" wrote:

      > 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!!

      #1; Sat, 07 Jun 2008 22:47:00 GMT
    • On Mon, 24 Oct 2005 12:21:03 -0700, Doug_C

      <DougC.ms-access.todaysummary.com.discussions.microsoft.com> wrote:

      >Hello,

      >I have a field on my form called "Total Mail Destroyed"

      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.

      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]

      #2; Sat, 07 Jun 2008 22:49:00 GMT
    • 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" wrote:

      [vbcol=seagreen]

      > 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" wrote:

      #3; Sat, 07 Jun 2008 22:50:00 GMT
    • 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" wrote:

      [vbcol=seagreen]

      > 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" wrote:

      #4; Sat, 07 Jun 2008 22:51:00 GMT