Adding Null fields

[AdminCyn]

I am trying to create a report to simulate an invoice. I have created a Select query to join all the tables/fields into one. That is working all good. I have set the Format to be 0;0;0;0 so that if it is a null field it prints a "0" instead. My issue is when there is a null value in a list of five number I want to total, I get back another null value; here is what my report looks like




How do I tell it to ignore the null value fields and add the ones with values?

[ADezii]

I am trying to create a report to simulate an invoice. I have created a Select query to join all the tables/fields into one. That is working all good. I have set the Format to be 0;0;0;0 so that if it is a null field it prints a "0" instead. My issue is when there is a null value in a list of five number I want to total, I get back another null value; here is what my report looks like




How do I tell it to ignore the null value fields and add the ones with values?

If any part of an Expression is NULL, than the entire Expression will evaluate to NULL. Put differently, NULLs will propagate through Expressions. You can use the Nz() Function to return a 0 should a Field be NULL, as in:
Nz(Field1) + Nz(Field2) + Nz(Field3) + Nz(Field4)...
That being said:

Debug.Print Nz(75) + Nz(NULL) + Nz(25) + Nz(NULL)

will return

100

[AdminCyn]

Brilliant! Works Perfectly :O)