Access calulation fails if any field is null

Question

I'm performing a select on a single table, and I need to total the value in a set of related field, I have this in the 'field' portion in the query builder.

TotalValue : [FIELD1]+[FIELD2]+[FIELD3]+[FIELD4]+[FIELD5]+[FIELD6]+[FIELD7]

When I go to the 'table view' for the query, I only get a calculation where all the fields specified have values? But if all the fields have a value it works perfectly.

In an attempt to see if this was realy the case I inserted each of the fields into the query, and I get a table that has missing values.

Obviously not what I was expecting!

Am I missing something obvious? Is it related to 'null' values in the fields?

please help, thanks in advance

David

Answer 1

You need to wrap each field with Nz(Field,0)

TotalValue : Nz([FIELD1],0)+Nz([FIELD2],0)+Nz([FIELD3],0)+Nz([FIELD4],0)+
Nz([FIELD5],0)+Nz([FIELD6],0)+Nz([FIELD7],0)

It is also possible to set up a table with a zero default for numeric values if null is not allowed.

Answer 2

The answer supplied by Remu got me to the correct answer.

I tried Remu's solution, and I got an error message, something about a extra ',' not being where it should be.

So a minor modification to the code, and now it reads...

NZ([DEBITN1D1])+NZ([DEBITN2D1])+NZ([DEBITN3D1])+NZ([DEBITN4D1])+NZ([DEBITN5D1])+NZ([DEBITN6D1])+NZ([DEBITN7D1])

I'm givin Remu a +1 as I didn't know about this particular function, nor could I find it anywhere! Also his/her response put me on the correct track for a solution.

David

Edit:

So in response to the comments, I heartily agree that the extra info should be included. However I can see no reason why the code failed in my case when I had the extra variable in the method?

In light of this I find it difficult to select my response as the correct answer (even though Remou's original failed with an error and my code did not).

I wonder if this is because I am using the function in the field details in the query builder (as opposed to using it in a VB module on a report or page). Does anyone have any way that I test to see what the cause was?