#Error in calculated footer in report Access 2003

[Spider]

Hi all,
I am trying to hide the #ERROR in the footer of a report that has no data.
These are patients that had no complications for a date range. I do want to display the report and I have used =IIf([epPtID],Null,"There are no complications for this period.......") in the body of the report.

I’ve tried researching IIF, NO DATA etc but can’t find one that works for my scenario as common as it must be.. I have handled zero and null before – but not ERROR.

I tried code to change null to 0 – but I'm not very good at that and wasn't sure that it was really null? There were no complications. If I do need to use VB - I was not sure where to put that.
Thanks, and Happy Christmas!
Vicky

[HansV]

You mention that you used

=IIf([epPtID],Null,"There are no complications for this period.......")

in the body of the report, but what did you use in the report footer? If we have more details, we may able to solve it without VBA.

It *can* be done with VBA too, in the On Format event of the footer section of the report:

Code: Select all

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
  Me.txtSomething.Visible = Me.HasData
End Sub

where txtSomething is the text box you'd like to hide if there are no data.

[Spider]

I tried different combinations of:
=Nz([MTotal],"NA")
=IIf(IsNull([MTotal]),"NA",[MTotal])

But I just got this to work....
=IIf(IsNull([epPtID]),"NA",[MTotal])

I have been working on this all morning! I will keep your code in case I still have problems with this.
I appreciate you all very much!
Vicky