Help Calculating form fields

[scorp1]

Hi,

I am trying to calculate some figures on a form in a subtotal field, I have used the control source and built an expression. the problem is that the table dose not update with the calculated amount?? Some how I need to have the control source as subtotals so that data is entered into the table and make it calculate at the same time I.e.

= Subtotal «Expr» [Amount1] + [Amount2] + [Amount3] + [Amount4] + [Amount5]

this comes up with an error

Please can anyone help ???

 

[Rich]

Aa a general rule of thumb you should not store calculated values in a table. The control source of your subtotal text box should be
=[Amount1]+[Amount2]+ etc.

 

[scorp1]

Thanks,

But........................ how do i keep a record of the calculations ?
I am trying to make an invoice database that stores the invoice once they have been input by a user.
The problem is I need a few calculations and the results of which need to be recorded along with the invoice data ??????????????

 

[Jeff Bailey]

You really shouldn't store calculated results. In general it's better to store all the amounts used in the calculation (qty, unit cost, tax rates if applicable, and so on) and do the calculation again if a reprint of the invoice is required.

It is safer to do it this way as you are less likely to introduce the kind of error where one of the components of the calculation has to be edited but you forget to modify the result of the calculation. You MUST (of course) remember to store all the components of the calculation (which you should be doing anyway). You cannot use the current cost price, for instance, because in a few months time that may well be different, making it impossible to recreate the original calculation.

Having said all that, I don't know of anyone who doesn't occasionally 'break the rules' for convenience sake (or some other reason). If you go down that route be VERY careful to safeguard the 'integrity' of your calculated results.

Bear in mind that it is generally MUCH easier to modify calculations when you have all the components stored than when you have the result but not all the parts that were used to produce that result (if you do have all the necessary parts there's no real point to storing the result is there?)

Good luck

Jeff

 

[scorp1]

Jeff,

Thanks, how do I do that ? I have fields that have the amounts and all I want to do is subtotal total and VAT. the user needs to input the amounts and the calculation should show the respective result i.e.

· Amount1 is 5 no calculation entered in table
· Amount2 is 5 no calculation entered in table

· subtotal is 10 =sum([amount1]+[amount2]) result needs to go into a table or saved somewhere

· vat @ 17.5
· Vat result is 1.75 =sum(([subtotal]*[VAT@])/100)
result needs to go into a table or saved somewhere

· Total result is 11.75 = sum([vat]+[subtotal]) result needs to go into a table or saved somewhere

All the above I need to keep in a table, this being my problem can anyone suggest how I can do this? The user needs to be able to search the records and pull up the information in its calculated form. Any suggestions would be welcome I am well and truly stuck on this one…..

 

[Jeff Bailey]

Well, I'd store the user entries, along with other info (user ID, Entry Date etc.) and make sure there is a unique key as well (you can use an Autonumber).

If you do that you can still output the results as you currently do, just don't save them. You need to be able to identify an entry if you want a reprint later. You could store a unique Invoice Number or suchlike (instead of the autonumber maybe, or perhaps as well as).

Jeff