I have a report and the details section returns no record. That's fine because no criteria is matched in the query. However, in the report footer of the report i do sum([oneofthecontrols] and shows #error because nothing to sum.

Is it possible to set this to zero(0) either in the details section or the control in the report footer and not have #error as i use this figure in another report which then also shows error but with zero would get around the problem.

can any one please help.

TIA

ishabir

try this as your control source:

=NZ(Sum[fieldname]),0)

which converts nulls to 0's

HTH
Ben

thanks Ben, i tried it in the textbox on the form footer which sums a field in the detail section and still get #error.

ishabir

OK, let's try this:

=Sum(NZ([fieldname],0))

nope, still the same.

I admit I am stumped...

Ben

Maybe if i try to put zero in the desired field in the query then then reports might work.

here is my field in my query table1.[stop_duration] which i changed to nz(table1.stop_duration) and does not work.

Do you think its possible to do it here?

thanks

ishabir

It might be better if you used the No Data event to prevent the report from running.

no i need to have a zero in stop_duration when no records because i am calculating values elsewhere based on stop_duration. Is it possible to use NZ and or IIF to get a null to be a zero.

thanks

ishabir

Either

Nz([field],0)

or

IIF(IsNull([Field]),0,[Field])

should work

Hi

I was just facing exactly the same problem and found your post.
Although i agree the solutions above should work they don't however I have since solved the problem with the following code:

=IIf(IsNull([myfield]),0,Sum([myfield]))

The key seems to be placing the Sum function where I have rather than putting it inside the IsNull function or before the IIf .

Hope this helps
Mark