Zero record reports '#error' 1

[Tarnish]

Hi,

I use IIf and/or Nz expressions to populate a couple of fields on a report...one being a group header and another being a totals column. They work fine except when there's a report that has zero records. In that case, they both produce a '#error' where normally there'd be either a word or a number (depending on the field).

Since my users may want to print reports even when they don't contain records, is there a way around this?

Thanks,
T

 

[PaulBricker]

Try turning them into empty stings.

Nz(FieldName,"")

or

IIf(IsNull([FieldName]),"",[FieldName])

Paul

 

[Remou]

It is possible to set the visibility of controls in the No Data event:

[tt]Private Sub Report_NoData(Cancel As Integer)
Me.txtTotal.Visible = False

End Sub[/tt]

 

[Tarnish]

Thanks for the quick replies. I'll give each a try.

T

 

[dhookom]

The Nz() and IIf(IsNull()) won't work if there are not records returned in the report. You can fix this with something like:
=IIf(HasData, Sum([YourField]), 0)
or
=IIf(HasData, Sum([YourField]), Null)


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Tarnish]

Thanks Duane,

I'll try that one.

T

 

[Tarnish]

Thanks again, Duane. Worked out well.

T