Formula to calculate textboxes - Form view

[santon]

Hello, I have a small problem with calculating the sum od 6 textboxes.





There are six textboxes named kg_1. kg_2. kg_3. ... (shown on the picture), default value 0, and set format "#,###" so that zero is not visible in form view.
Last textbox "ukupno_cig" is calculated field with formula "([kg_1]+[kg_2]+[kg_3]+[kg_4]+[kg_5]+[kg_6])" and when you enter the numbers in one six fields it automatically calculates the sum in he field "ukupno_cig". But when you delete numbers from one field, the textbox for sum is left blank. How can I fix that?

[mike60smart]

Hi

Use the NZ as shown:-

Nz([kg_1],0)+Nz([kg_2],0)+Nz([kg_3],0)+Nz([kg_4],0)+Nz([kg_5],0)+Nz([kg_6],0)

[santon]

Hi

Use the NZ as shown:-

Nz([kg_1],0)+Nz([kg_2],0)+Nz([kg_3],0)+Nz([kg_4],0)+Nz([kg_5],0)+Nz([kg_6],0)

Tried that in calculated field, returns this error




Where should I put that formula you wrote?

[ranman256]

Everyone should make the function: NZZ()

function Nzz(byval pvVal)
on error résumé next
NZZ=Nz(pvVal,0)
End function

Save a lot of typing in queries.

[mike60smart]

You should not be using calculated fields in the table.

Calculations are usually carried out in queries that Forms are based on.

You would then create a Field in the Query to make your calculation.

Can you upload a zipped copy of the Db ?

[Minty]

This is a fundamental design problem.

You should have (possibly child) records that are individual weights.
These would then be very straight forward to add up.

What happens when you need KG_7 ? Answer: you have to redesign your whole database to accommodate it.

[santon]

Thank you all for your advices.
I tried to put the function NZZ in user functions and I also tried that formula in control source of texbox and it works, but then it doesn't save the calculated number in the table, it only shows on the form view.
Sorry for being a noob, still in learning process