No Data and Report Error

Report Source Query or Table can end up with no output records. In that case some of the controls with formula on the Report will show #Error on them. An image of a sample report is given below:



The #Error at the right top is a control that has a formula for showing the Period for which the Report is currently prepared for. The controls to the right of the word TOTAL are Sub-Totals and Totals of detail line values and all of them ended up with Errors because the underlying Query of the Report doesn't have any record to show for the current period selected by the User.


Even though this is not a serious issue if it became necessary to show this Report to some one as a NIL REPORT or printed and kept for future references leaving the controls with errors this way is not in good taste on the part of the Database Developer.


The modified version of the above Report is given below with a Comment, Summary Controls showing Zero Values and Report period printed correctly.



I have made few modifications to the Report Design to add a hidden label at the footer of the Report with the Caption: *** Congratulations *** Nothing Pending to show up when there is no output Records for the Report. The Visible Property of the label is set to No manually. On the Detail Section Under the Description Column shows *** NIL REPORT ***. The period for which the Report is prepared is also shown to the right, above the Detail Section headings.


Report Period (DateFrom and DateTo) are normally entered into a Parameter Table and joined with the Report Source Table in a Query to use them for criteria and for displaying on the Report.



I have created two Text Controls (with the name Frm and To respectively) at the Report Header Section to the right of the Control name STAFFNAME to load the DateFrom and DateTo Values from the Report Parameter Table with DLookup() Function:



=DLookUp("DateFrom","Report_Param")

Second Control have the expression to read DateTo from the Report_Param Table and both values are used in the expression (="Period : " & [frm] & " To " & [To]) to format the values to show the output as in the second image given above.


These are all cosmetic changes for the Report. The major change is to create a temporary table with a single blank record, with the same structure of the Source Table or Query that is attached to the Report. If your Report is using a Table as Report Source Data then make a copy of the structure of the Table and add a tmp_ prefix to the table name like tmp_myReport. If it is a Query then create a Make-Table Query using the Report Source Query and create a temporary table. Add a blank record in the temporary table. If your Report Table has a Text Field that is displaying value on the Report then type *** NIL REPORT *** in that field. Keep all other fields Empty.


The trick is, when the Report is open by the User we will check whether the original Report Source Table or Query have any record in it or not. If not swap the Temporary Table with the Report Source Table or Query. The hidden Label's Visible Property will be set to Yes to display the comment *** CONGRATULATIONS *** NOTHING PENDING. Since the temporary table has one blank record in it already, the Summary Controls will not end up with errors.


We need a few lines of VBA Code in the Report_Open() Event Procedure to check and swap the Report Source Table or Query.

Private Sub Report_Open(Cancel As Integer)
Dim i
i = DCount("*", "myReport")
If i = 0 Then
   Me.RecordSource = "tmp_MyReport"
   Me.lblMsg.Visible = True
End If

End Sub

Copy the above lines of Code in the Report's VBA Module and make changes to insert the correct Table/Query and tmp_myReport names.

---

Opening External Data Sources
PIE Chart Object and VBA
Column Chart and VBA
Working with Chart Object in VBA
Linking IBM AS400 Tables