Adding fields sum (1 Viewer)

[Gismo]

Hi all,

Quick question
Below is a table with temporary data
I need to add fields together
Query comes out blank
What am I doing incorrectly?

Profit/Loss: Sum([Financial Summary Tbl]![Total Sales (UGX)]-([Financial Summary Tbl]![Total Purchase (UGX)]+[Financial Summary Tbl]![Salaries]+[Financial Summary Tbl]![Accets]+[Financial Summary Tbl]![Promotions])-[Financial Summary Tbl]![Stock Adjustment])

 

[Guus2005]

[Total Sales (UGX)] = NULL so the end result is NULL
same goes for ![Total Purchase (UGX)].
Use Nz to circumvent this problem

Profit/Loss: Sum(Nz([Financial Summary Tbl]![Total Sales (UGX)])
                -(Nz([Financial Summary Tbl]![Total Purchase (UGX)])
                 +Nz([Financial Summary Tbl]![Salaries])
                 +Nz([Financial Summary Tbl]![Accets])
                 +Nz([Financial Summary Tbl]![Promotions]))
                 -Nz([Financial Summary Tbl]![Stock Adjustment]))

 

[Gismo]

[Total Sales (UGX)] = NULL so the end result is NULL
same goes for ![Total Purchase (UGX)].
Use Nz to circumvent this problem

I sum them all together, for November, there is figures for all

Just Profit/Loss is blank

But NZ seems to work just fine
Thank you

 

[Guus2005]

did you try using Nz?

If so, split the problem into smaller pieces.
Add a column profit/loss per record and see if there is any result before summing it all together.

 

[Pat Hartman]

Just to clarify, aggregate functions and domain function in forms, reports, queries ignore nulls but plain old arithmetic fails unless YOU handle potential null values.

So, Sum(a) will produce a correct result even if a contains null values but Sum(a + b) will not produce correct results if either a or b has even one null value). The second expression needs to be Sum(Nz(a,0) + Nz(b,0))