Summing Null values

[vipersmind]

Hi
On my report I have the following fields:
[PEOri$Cap]
[PEDrill$Cap]
[PESurvey$Cap]
[PEStandBy$Cap]

The control source for the report is a qry.

[TotalPECap$] is the total for above fields.
control source for this one is:
=[PEOri$Cap]+[PEDrill$Cap]+[PESurvey$Cap]+[PEStandBy$Cap]

My problem is when the query returns no values at all I get blanks in the first 4 fields and #error in the [TotalPECap$]

Although I would like to see "-" in the 4 fields when nothing is returned from the controlling qry , I really want to show $0.00 when the total for these 4 fields is returning null.

So I tried the following in the control source of [TotalPECap$] to force it to show 0, but no, it won't play!

=NZ([PEDrill$Cap],0)+NZ([PESurvey$Cap],0)+NZ([PEOri$Cap],0)+NZ([PEStandBy$Cap],0)

can you please help

 

[vipersmind]

Worked it Myself

Stop the panic I worked it out

In the end to account for the fact that I could return a zero value and or an error I came up with the following:

=(IIf(IsError([PEDrill$Cap]) Or ([PEDrill$Cap]=0),"-",[PEDrill$Cap]))+
IIf(IsError([PESurvey$Cap]) Or ([PESurvey$Cap]=0),"-",[PESurvey$Cap]))+  ......and so on

trail and error wins out again

 

[vipersmind]

one more possibility

Thought might as well add this!

I ran my report a few times with different datasets and had the problem where when all of these fields
[PEOri$Cap]
[PEDrill$Cap]
[PESurvey$Cap]
[PEStandBy$Cap]
were forced to show "-" the total [TotalPECap$]gave #error

so to counter this I used

=IIf((IIf(IsError([PEDrill$Cap]),0,[PEDrill$Cap])+IIf(IsError([PESurvey$Cap]),0,[PESurvey$Cap])=0),"-",[PEDrill$Cap]+[PESurvey$Cap])

which says if all of the summing equals 0 show "-".
which is what I wanted.
Hope someone can use this sometime.
Cress