Mathematical Calculations in a form linked back to a table

  • Thread starter Thread starter Graeme
  • Start date Start date
G

Graeme

Hell
I palnning on implementing calculation between two fields, and the result within another field. Suppose I have three fields called A B and C respectively. In the form I plan on Inputing a value for[A] and Inputing a value for . Now, Field [C] will calculate [A]-. When filed [C] is calculated, it is not in the table, like A and B are. I would like to know how to solve this problem.
 
It is rarely necessary to store a calculated value in a table in Access.
Much more commonly, a form (as you are using) or a query is used to do the
calculation "on the fly", as it were.

Since the values in either [A] or could change, what about the idea of
adding code to the AfterUpdate event of BOTH of those controls,
recalculating the displayed (but not stored) value for the control [C]?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
You may find it best not to store Field C in the table at all.
Storing fields that are calculated from other fields can be a little
dangerous for data integrity. If someone updates Field A or B, Field
C could then be storing the incorrect value.

Instead, just calculate C whenever you need it via a query/report.
 
Graeme -

First, is control [C] bound to a field in the table?
Second, when are you calculating [C]? It should be before the record is
saved (e.g. in the BeforeUpdate event on the form (not on the BeforeUpdate of
a field).
 
Hello
The fields A B and C are all bounded to the same table. I am calaculationg C the values A and B are being intered into the form. First I input a value for A, than I input a value for B, and the result comes in C.
 
The fields A B and C are all bounded to the same table. I am calaculationg C the values A and B are being intered into the form. First I input a value for A, than I input a value for B, and the result comes in C.
Click to expand...

Well, just let me reiterate what everyone else is correctly telling you:

Field C *SHOULD SIMPLY NOT EXIST IN YOUR 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 in the control source of a Form or a Report
textbox.
 
You may be facing one of those quite rare situations in which it it
appropriate to store a calculated value... but you haven't given us much to
go on.

If you'll provide a more specific description of what you are dealing with,
folks here may be able to offer more specific suggestions.

If you don't, then the odds are you shouldn't do it the way you're trying
to...

More information, please ... or not!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Graeme,

The How To:

For your form(s),
Switch into Design View.
Add a TextBox to the form.
In the data part of the field, type =[A]-
In the label part of textbox, type your label.
Exit Design View.

For your reports, do the same.

For more complicated calculations remember what you learned in algebra about
the associative rule and parentheses. Test your calculations with sample
data.

HTH

David
 
Back
Top