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?