Calculated values embedded in table (1 Viewer)

[mtagliaferri]

I am trying to make some calculations in a form, and the result od the calculation being written as a result in a table, I am aware that a query would be the best approach however I need some calculated values available in the form.

I have a field NetPay in my table where I want to store the sum of: [BasicSalary]+[AdditionalAllowances]+[Adjustment]-[PensionContributions]-[TaxPaid]-[NI].

Any guidance on this would be appreciated.

 

[arnelgp]

if all those on your Expression are fieldname, you can put the Expression on NetPay (Calculated field).

 

[mtagliaferri]

if all those on your Expression are fieldname, you can put the Expression on NetPay (Calculated field).

Yes they are all Fieldname, you mean to change NetPay in my table to Calculated Value and place the formula there?

 

[arnelgp]

Yes they are all Fieldname, you mean to change NetPay in my table to Calculated

NetPay should be Calculated field, then place your formula there

you can use this Expression:

Iif(IsNull([BasicSalary]), 0, [BasicSalary]) +
Iif(IsNull([AdditionalAllowances]), 0, [AdditionalAllowances]) +
Iif(IsNull([Adjustment]), 0, [Adjustment]) -
Iif(IsNull([PensionContributions]), 0, [PensionContributions]) -
Iif(IsNull([TaxPaid]), 0, [TaxPaid]) -
Iif(IsNull([NI]), 0, [NI])

i use Iif() function because if you have Null value on either
one of those field,s you will get unexpected result.

 

[Minty]

Can't you use Nz() to make it a bit more versbose?

Nz(BasicSalary,0) + Nz(AdditionalAllowance,0) etc. etc.

 

[plog]

[BasicSalary]+[AdditionalAllowances]+[Adjustment]-[PensionContributions]-[TaxPaid]

Are all those fields in a table? Or are they just controls on an unbound form?

 

[The_Doc_Man]

I am aware that a query would be the best approach however I need some calculated values available in the form.

You can - and probably should - use a query to drive a form anyway. Forms will happily use anything that produces a recordset, and that includes tables and SELECT queries. In fact, even for single-table forms, I have found that if you are dealing with a linked back-end file, sometimes you get better results with a query. Sometimes if you use a direct-to-table-via-external-link, you get asked "Where is table XXX?" when opening the form - but the use of a query bypasses that question.

Not only that, but if you wanted a computed value on your form (or a report), you can define the .ControlSource for the particular computation control to be an expression that adds the multiple fields together, in which case they would be computed at the time of the Form_Current event. As long as the multiple fields are available in the form's .Recordset, you can reference them even if they are NOT individually represented by controls on the form.