Solved Null Value

[Seph]

Good day everyone.

Please could you lend a Machete to my intellectual bush.

I have an Invoice form that calculates values from 3 tables (Master Fields Linked by Primary Key).

However, whenever a single table (Cost in this case) has no records. The Calculation fields stop working.

I've tried adding a NZ function to the Cost Total Sum Text Field (No Success):

I've Tried adding a NZ function to the Cost Total Field in the Query that the form is sourced to (No Success):

I suspect the issue here is that due to the fact that there is no records captured for Costs linked to the Primary Key, Access can't populate any information.
Which is perfectly sensible. However, how can I get it to replace vacant records with 0 so the formula's can continue to function as there won't always be Costs included in all jobs.

Appreciate any ideas.

Thanks

 

[arnelgp]

use this Expression on the textbox on the Footer section:

=Nz(Sum([CostTotal]), 0)

 

[CJ_London]

Possibly need to left join to your costs table?

 

[Seph]

use this Expression on the textbox on the Footer section:

=Nz(Sum([CostTotal]), 0)

You sir are a legend.

Spent hours trying to figure it out before visiting here.

Really appreciate your assistance!

 

[Seph]

Possibly need to left join to your costs table?

Thanks for your assistance, arnelgp's suggestion worked.

 

[Pat Hartman]

=Sum(YourField) ---- ignores null values so you don't need the Nz()

However,

=Sum(Fld1 + fld2) --- will REQUIRE the Nz() if either field might be null
=Sum(Nz(fld1,0) + Nz(fld2,0))

If you want the final result to show 0 rather than blank, that's when you put the Nz() around the whole expression.

=Nz(Sum(YourField))
=Nz(Sum(Nz(fld1,0) + Nz(fld2,0)),0)

In summary, the aggregating functions ignore nulls BUT, for other arithmetic, you need them.