Summary count if no records gives #ERROR not 0 2

[Roncayenne]

I have an Acess query that works ok. Problem is at the end of the report I want a total count of all records. Each record has one ID, so in the total field 'Control Source' properties I have =sum(id) which works fine if I have any records and gives me a total count in the report.
If there are no records it gives me #ERROR. I am trying to get if no records, a '0' count. I have tried using a IIf stmt in the Control Source and it seems that works fine if there are records, but doesn't if no records from the query.

Probably easy solution, but its got the best of me! Can anyone help?
Thanks!
Ron

 

[Remou]

Hi
Have you looked at the On No Data event? It might be suitable.

 

[Roncayenne]

When I click on the "Event" table on the properties of the field, it is blank. How can I setup an On No Data event?

 

[Remou]

Hi - It is an event for the report itself.

 

[Roncayenne]

Sorry, after I sent the msg and started going through google, found what you meant. I set that up and it is like you mentioned, when I run the report I get a message that I put in of 'No Records in Report'.

You would not know of a way to where I can generate the report and in the field that I mentioned, if no records, have something like "TOTAL IDS: O or "No Records Found" rather than "TOTAL IDS: #ERROR

The On No Data works and I appreciate the comeback on this.
Thanks!

 

[Remou]

Yes, I think so. I think this might be easiest:

Private Sub Report_NoData(Cancel As Integer)
  DoCmd.OpenReport "A_Blank_Report" 'Choose print or preview here
  Cancel = True
End Sub

 

[Roncayenne]

I tried what you mentioned and get a Run Time Error 2585 -
This action cannot be carried out while processing a form or report event'.
I put this in the On No Data Event.


Ron

 

[Remou]

Oops. I can't see what I've missed at the moment as I'm away for a few days, though I did test. Hmm.

 

[dhookom]

There is no reason to add any code if you don't want to close the report. Simply replace the control source with

=IIf([HasData],Sum(id),0)

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]

 

[Roncayenne]

Duane,
Tried what you suggested and it works fine. If no recs are found it puts out the report with the total id = 0 rather than #ERROR.

Both what you and Remou work fine. Two different approaches for same result..

Thanks to both of ya'll for the help....

Ron