No Data and Report Error

Introduction.

Report Source Query or Table can end up with no output records. In that case, some of the controls with the formula in 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 preparing. The controls to the right of the word TOTAL are Sub-Totals and Totals, 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 someone 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 show Zero Values, and the Report period is printed correctly.

I have made a 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 are 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.

The Report Period (DateFrom and DateTo) is 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.

Few Changes in the Report

Created two Text Controls (with the name From and To respectively) at the Report Header Section to the right of the Control name STAFFNAME to load the DataFrom and DateTo Values from the Report Parameter Table with DLookup() Function:

=DLookUp("DateFrom","Report_Param")

Second Control has 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 the cosmetic changes required in the Report.

Temporary Table for Report.

The major change is to create a temporary table with a single blank record, with the same structure as 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 the value on the Report then type *** NIL REPORT *** in that field. Keep all other fields Empty.

How the Trick Works

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.

Few Lines of VBA Code

Private Sub Report_Open(Cancel As Integer)
Dim ii = 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