#Error Message in Report

[RMP]

The query parameters correctly return no records. The report that is run
based on this query returns #Error in the report controls.
What is syntax to use so that when the query correctly returns no records a
Zero is displayed in the report instead of #Error.
Thank you for your help on this.

 

[Duane Hookom]

You can use a control source like:

=IIf([HasData], Sum(Qty),Null)

 

[fredg]

The query parameters correctly return no records. The report that is run
based on this query returns #Error in the report controls.
What is syntax to use so that when the query correctly returns no records a
Zero is displayed in the report instead of #Error.
Thank you for your help on this.

If the report has no records, the usual method is to just cancel
running the report.

Code the Report's OnNoData event:
MsgBox "There is no data to report on."
Cancel = True

If you have opened this report from a code event, this will raise
Error # 2501, so you need to trap that error in the event that opens
the report:

On Error GoTo Err_Handler
DoCmd.OpenReport "ReportName", acViewPreview
Exit_This_Sub:
Exit Sub
Err_Handler:
If Err = 2501 then
Else
MsgBox "Error #: " & err.Number & " " & err.Description
End If
Resume Exit_This_Sub

 

[RMP via AccessMonster.com]

Duane,

Thank for the tip that let to the solution.

I had to fields in my query {Amt} and [DetailAmt] which for the time period
specified there were correctly no records in the query and the report
generated #Error in the controls.

Using your suggestion, I modified my report controls as follows:

Iif([HasData], [Amt],0) and Iif([HasData],Sum([DetailAmt]).0)

Appeciate your help on this.

BTW, I looked on Microsoft's Access website for a solution and wasn't able to
find it. Can you recommend a good reference book?

Regards,

Richard

You can use a control source like:

=IIf([HasData], Sum(Qty),Null)

The query parameters correctly return no records. The report that is run
based on this query returns #Error in the report controls.
What is syntax to use so that when the query correctly returns no records
a
Zero is displayed in the report instead of #Error.
Thank you for your help on this.

 

[RMP via AccessMonster.com]

FredG,

Thank for your help.

In my case I needed to generate a report even though there are no records.
(Has to do with individual sales performance).

But I have noted your solution for future reference when I do not wish to
generate a report im the event of no records returned in the query.

BTW, I looked on Microsofts Access website for a solution and wasn't able to
find one. Can you recommend a good reference book that provides in depth
information on how best to use the Access controls to solve problems?

Thanks,

Richard

 

[Duane Hookom]

Check www.viescas.com for some recommendations. I have a couple of John's
books and they are very good. In particular, his "Building Microsoft Access
Applications " contains four complete applications built by John.

--
Duane Hookom
MS Access MVP

Duane,

Thank for the tip that let to the solution.

I had to fields in my query {Amt} and [DetailAmt] which for the time
period
specified there were correctly no records in the query and the report
generated #Error in the controls.

Using your suggestion, I modified my report controls as follows:

Iif([HasData], [Amt],0) and Iif([HasData],Sum([DetailAmt]).0)

Appeciate your help on this.

BTW, I looked on Microsoft's Access website for a solution and wasn't able
to
find it. Can you recommend a good reference book?

Regards,

Richard

You can use a control source like:

=IIf([HasData], Sum(Qty),Null)

The query parameters correctly return no records. The report that is run
based on this query returns #Error in the report controls.
What is syntax to use so that when the query correctly returns no
records
a
Zero is displayed in the report instead of #Error.
Thank you for your help on this.