Calculated Field question-need help asap

[Guest]

I have several calculated fields on my form presently. At the bottom of the
form I have a final Calculated field that sums up several others. I need to
use this last calculated field amount for other queries and reports. It is
quite a complex one as several of the other fields that it uses for the final
calculation would need to be in case and if..then..else statements. Not sure
what I should do. I know that you don't want to store calculated fields in a
table, but not sure what else to do. Any and all help is greatly appreciated.

Thanks,
Todd

 

[Al Camp]

Todd,
I don't see any problem in saving the results of a calculated field... if
the circumstances warrant it.
I've done it on very difficult calculations, where many tables and
queries and coding had to come together to do it on a form, and I didn't
want to have to repeat that complicated process in subsequent forms,
queries, and reports.
As long as the calculation is static and will never change... save it to
a field if you want...

hth
Al Camp

 

[Al Camp]

Sorry I didn't get back to your post sooner... hope you haven't given
up...

Let me use the example of Price * Qty = LineTotal, and you want to
save LineTotal.

You'll need to add a field LineTotal to your table to hold the calculated
value... I take it you've done that already.
Place a bound text control (ControlSource = LineTotal) on the form.
Now... using theAfterUpdate event of both Price AND Qty, trigger this
code...
LineTotal = Price * Qty

When either Price or Qty are changed, the LineTotal field value will be
updated, and saved in the table. I realize your calculation is more
complicated, but you'll just have to apply this concept to all the elements
that go into your particular calculation.

hth
Al Camp