#Error is returned on sum([FieldName])

[beh]

I'm trying to get rid of the #Error on a sum field in an Access report. I've
tried =Sum(IIf(IsNull([CostDiff]),0,[CostDiff])) and the nz function to no
avail. It looks like to me like the reason that it is doing it is because
the quey is not returning any records. The query is right, there are no
records to return. I need the report to show 0 instead of #Error in this
instance. Any help is appreciated.

 

[Duane Hookom]

Try:
=IIf([HasData] = -1, Sum([CostDiff]),0)

 

[Marshall Barton]

I'm trying to get rid of the #Error on a sum field in an Access report. I've
tried =Sum(IIf(IsNull([CostDiff]),0,[CostDiff])) and the nz function to no
avail. It looks like to me like the reason that it is doing it is because
the quey is not returning any records. The query is right, there are no
records to return. I need the report to show 0 instead of #Error in this
instance.

It seems to me that a report with no records is not going to
be very useful ;-)

Maybe your report is more complex than you have stated in
your question? If the Sum is in a subreport, then you
should read up on the HasData property in VBA Help and
provide more details in a follow up question.

 

[beh]

Thank you. That worked. I've never seen the hasdata before.

Try:
=IIf([HasData] = -1, Sum([CostDiff]),0)

--
Duane Hookom
Microsoft Access MVP

I'm trying to get rid of the #Error on a sum field in an Access report. I've
tried =Sum(IIf(IsNull([CostDiff]),0,[CostDiff])) and the nz function to no
avail. It looks like to me like the reason that it is doing it is because
the quey is not returning any records. The query is right, there are no
records to return. I need the report to show 0 instead of #Error in this
instance. Any help is appreciated.