I have a report which sums the balances of the records that the report returns but when the report returns no records that meet the reports criteria I get an error "#Error" where the formulas are. Is there a way for me to have an overide to report something like "0" instead of "#Error" when there are no results to show since it makes it seem that the report is innaccurate when in fact it's working perfectly. Thanks.

Have the following code in the reports on no data event:

Private Sub Report_NoData(Cancel As Integer)
MsgBox "No records match the criteria you entered.", vbExclamation, "No Records Found"
Cancel = True
Exit Sub
End Sub

You can modify the above code to change the control source of the offending textbox to "=0" if you want instead of cancelling the opening of the report.

Help me understand what the above does? I need to have the report run so i can print it and include in my monthly reports, i just want it to say something on the report other than #Error in the totals. can the above be changed to make this happen?

Will there be any data in the report when the totals are zero? I thought the totals were zero precisely because there was no data to report. What my code does is to cancel the opening of the report when there is no data to report (cancel = true causes the report opening to be cancelled).

If you want the report to open anyways, just remove the cancel= true and put in your own code there (for example, me.mycontrolthathaserror.controlsource = "=0" or something like that).

i got your first code to work but I can't get the report to actually run but show something other than #Error in the formulas. I have to admit i'm not good at writing code so if anybody can assist me I would appreciate it. thanks.

What is the code you have currently?

what is the record source for your report? Where is the calculation being made in a group footer? Report Footer ?
How are the Totals being made?