Sum with Null Values

[janmack79]

I am trying to do a calculated field in a query to sum up three fields and below is my formula. However if there are nulls in any of the three field then it does not return a value. I read that you should put like NZ in front of each field in the calculation but that just returned an error message for me. Can someone advise what I would need to do to this formula to ignore a null value and sum up the other fields?



Contract Value Calc: [Reports]![Value]+[Reports]![Services]+[Reports]![Maintenance]

[andy49]

Should be nz([reports]![value],0) + nz(and so on...
what error you getting?


Sent from my iPhone using Tapatalk

[janmack79]

OK that worked perfect, thanks you!!

[Neil Bingham]

I am trying to do a calculated field in a query to sum up three fields and below is my formula. However if there are nulls in any of the three field then it does not return a value. I read that you should put like NZ in front of each field in the calculation but that just returned an error message for me. Can someone advise what I would need to do to this formula to ignore a null value and sum up the other fields?

Contract Value Calc: [Reports]![Value]+[Reports]![Services]+[Reports]![Maintenance]

Suggest you change default value (in table design view select field then change to default to 0 in box at bottom) of all the relevant number fields in your table to 0 (zero) then no problem with the arithmetic whatever reports, forms or queries now or in future. Null is not the same as 0 so cannot be used in arithmetic and computers have prob with doing sums with blank fields also!

[Micron]

Null means the value is unknown. Zero means zero (not Null and not Nothing). Thus zero is not Null. The suggestion may be appropriate for the particular database, but the two (0 and Null) should not be interchanged arbitrarily.

[Neil Bingham]

I am trying to do a calculated field in a query to sum up three fields and below is my formula. However if there are nulls in any of the three field then it does not return a value. I read that you should put like NZ in front of each field in the calculation but that just returned an error message for me. Can someone advise what I would need to do to this formula to ignore a null value and sum up the other fields?

Contract Value Calc: [Reports]![Value]+[Reports]![Services]+[Reports]![Maintenance]

Further my suggestion that you have default val as 0 in tables - you may need to do an update query to ensure all value fields in table are set to 0 (as I dont think - but haven't tested it - that it is retrospective if you change a field default)
Appreciate that now your forms and reports will show 0 instead of being blank - BUT totals will be correct. If you do not want to show the 0 values then use Conditional Formatting on the fields in the reports or forms that you do not want the 0's to show (an Expr such as [Field]=0) - if condition is met then font colour to be set to white - this effectively hides the 0's but shows all positive (and negative!) values but the arithmetic still works
Hope this more simple solution helps - I have found it a good idea to always have value fields in tables with a default value of 0 to avoid later math problems

[Micron]

You are correct. Adding a default value to existing data does not propagate throughout the existing data. In other words, it isn't retroactive.