Where is the best place to put formula's

[RunTime91]

Greeting All...



Just a question about technique regarding formulas and where is the best place to put them.

I have a table which derives its data from a user form with controls.

So I can see the calculated results as I enter the input data, I have 18 calculated controls on the form.

But my question is simply this...would it be better to put the calculations...

...in the table?
...or keep them in the controls?
...or add the calculations to the code?

Thank You for the education...

RT91

[Micron]

Table fields have very limited choice of expressions that can be used, so likely no.
Between controls vs code, I'd say it depends on how complicated the expressions are. If the control value is derived from a function (e.g. because it's complicated, takes parameters), then it's a no brainer - it has to be code.

I'd say the real question is how you managed to have calculated controls bound to table fields. AFAIK this is not possible. Maybe you didn't mean they were bound controls and the data goes into table fields via code?

I have a table which derives its data from a user form with controls.

[CJ_London]

Agree with Micron - it depends

If you are talking about a legal document such as a sales invoice, it is generally advised to store the values used in the calculation as well as the calculated values as well (not the calculation itself). The reason is if you need to reprint that document later, maybe years later you will get an exact replica. If you don't, prices change, tax rates change, addresses change so later it may not be possible to create that same document. The alternative is every time there is a change to the price, tax rate, address, etc it needs to be timestamped so when a document is reproduced for a given date, it can lookup the relevant values that were in force at the time of that date. But that still could produce a wrong result if the calculation has changed over time.

So (depending on requirement) I would not use calculations in a table, I would use either controls or functions (code) depending on what the calculation is. Or could be a mixture of both. Another alternative is to put your calculations in the recordsource query - it will recalculate as elements of the calculation are modified.

[RunTime91]

ugh... Yeah.. I said it wrong... Sorry about that...

Yes, currently the values are either entered into the controls or calculated within the controls and then passed to the table via code.

The formula's are basic arithmetic functions (+, -, /, *)... I think I will leave them in the controls

Micron ~ As always

Thank you so very much ~