reporting if no data found

[krberube]

Good morning all,
I have a report with several calculated fields in it. When I run the report for a date range that returns a message box "no data found" the report shows up with "#ERROR" in all the calculated fields.
Is there a way to just show 0 in these fields instead of the error message?

Thanks
Kevin

 

[KenHigg]

Use isnull() or nz()?

 

[krberube]

Hi Ken,
here is the calculation I use in 1 of the fields
=([closed]-([SW]+[NPF]))

Not sure how to get the isnull() or nz() incorporated into this.
Kevin

 

[gold007eye]

try using the IF / THEN statements to incorporate it into your code. You might want to use it in the On Open event on the report that checks to see if the field is null and if so changes the value to 0.

 

[KenHigg]

=(nz([closed])-(nz([SW])+nz([NPF])))

I think this should work...

 

[krberube]

sorry Ken, still shows #ERROR.
Gold007eye, I will try to fool with the IF/THEN.

 

[KenHigg]

Hum...

For starters you could lose a lot of the the parens...

=nz([closed])-nz([SW])+nz([NPF])

 

[gold007eye]

Not sure of the exact code but I believe it would be something like this:

If IsNull [FieldName] Then
Me![FieldName].value = 0
End If

 

[Mile-O]

Personally, I'd check if it will return any records before opening the report by using an IF THEN ELSE ENDIF structure on a command button's Click event wherein I evaluate a DCount().

 

[krberube]

Still trying guys.
Do you think there may be something I can do in the "on no data" event of the report?
thanks for the ideas so far.

EDIT:
SJ, could you elaborate on that for me abit?
Thanks

 

[Mile-O]

If DCount("*", "QueryName")  = 0 Then
    MsgBox "No records found.", vbExclamation
Else
    DoCmd.OpenReport "ReportName", acPreview
End If

 

[krberube]

i understand what your saying now. I placed this in the clk event for the button and now I receive a message
"run time error 2001 you canceled the previous operation"

The error line is:
If DCount("*", "my query is here") = 0 Then

Thoughts?

 

[Mile-O]

Are there parameters in the reports underlying recordset?

 

[krberube]

yes, the query uses a date range entered into a form for retrieving the data.
if thats what you mean.?

 

[krberube]

I think I am going to change this report abit. If I can figure out how to get code to check for NO DATA, then if no data, run another report which will have no calculated values and give just a few static items.

Would SJ's Dcount idea be a good way to check for data, then do something else?

Thanks for all the help
Kevin

 

[krberube]

OK, I was able to get Dcount to verify there is data or not, and with the If/Then run a report based on the answer. This worked out great, thanks for the ideas everyone!

If DCount("ShipDate", "ServiceSummaryByCust") = 0 Then
MsgBox "No Data found, You will get a summary version.", vbExclamation
stDocName = "ServiceSummaryByCustomerNoDetail"
DoCmd.OpenReport stDocName, acPreview
DoCmd.Maximize
DoCmd.RunCommand acCmdZoom100
Else
stDocName = "ServiceSummaryByCustomer"
DoCmd.OpenReport stDocName, acPreview
DoCmd.Maximize
DoCmd.RunCommand acCmdZoom100
End If

 

[Rich]

Use the NoData event of the Report

 

[jlocke]

No data

in the on no data event

Private Sub Report_NoData(Cancel As Integer)
MsgBox "There are no records to report", vbExclamation, "No Records"
Cancel = True
End Sub

 

[krberube]

Thanks guys, I will revisit the ON NO DATA EVENT when I complete the items on my "now" list. It sounds like that will also be a good way to do it.

Kevin