No Data question

[danbl]

I have a report that when there is no qualifing data prints with #ERROR in all the calculated fields. The calculated fields include counts, sums, and grand totals. (When thre is data the report works as expected.)

How can I either have the calculations show 0's or have the report print with a statement "No Data to Report"?

 

[rainman89]

What version on access are you using? 2007 has a event for No Data

 

[the_net_2.0]

you might also be able to use the "iif(iserror())" combination to control it. Or, control it with your calculations itself, and an outside iif() statement to substitute in case of a situation that brings in no data. for instance, a division by 0.

iserror() may be reserved for vba only, I'm not 100% sure

 

[danbl]

Thanks for the quick reply ...... I am using 2003 which has the "On NO Data" event, but not well versed in VBA yet. I have also tried an IIf condition in the calculation with no change in the result.

 

[rainman89]

In 2000 I used to do it like this

Sub Report_NoData(Cancel As Integer)
    MsgBox "There is no data for this report. Canceling report..."
    Cancel = -1
End Sub

 

[danbl]

That works ..... GREAT ... it ommits the report all together.

What would be the language to print "No qualifing Data" in the body of the report itself?

 

[rainman89]

Is there a specific reason that you would want that to print?? That would be a waste of paper if you ask me!

 

[danbl]

The reason to print this is to comply with State and Fedral Licensing requirements to show that there were no instances where the criteria was met. I agree it is rediculous!

 

[rainman89]

Ok So what if you create a label on the report and set it to visible=false then in the No Data set it to visible=true? and maybe hide everything else?

 

[danbl]

Makes sense ... I am a novice at this .... I placed a lable in the detail section but the only choices I have for Visible is yes or no. This is true for all the fields in the report.

?????

 

[rainman89]

sorry. You can use yes or no. Then in the report no data event put

your label.visible=yes

then all others=no

 

[danbl]

Thanks ... I will continue to work on this and be back if I have more questions.

Appreciate the help