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. An image of a sample report is given below:

Right-click to open Large Image in New Tab/Window.

The #Error message at the top-right corner appears because the control contains a formula intended to display the reporting period. Similarly, the controls to the right of the word TOTAL—which display subtotals, totals, and detail line values—also show errors. This occurs when the report’s underlying query returns no records for the period selected by the user.

Although this is not a critical issue, it is considered poor practice to present or archive such a report with visible errors, especially if it needs to be shared as a NIL REPORT or retained for audit or future reference.

The modified version of the report shown below resolves this issue. It includes a clear comment, displays zero values in the summary controls, and correctly prints the reporting period.

Right-click to open Large Image in New Tab/Window.

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. In the Detail Section under the Description Column, it 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.

Right-click to open Large Image in New Tab/Window.

Few Changes in the Report

Created two Text Controls (with the names From 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 the 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 creating a temporary table with a single blank record, which should have the same structure as the Source Table or Query, and attaching it 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. Fill numeric fields with 0 and keep all other fields Empty.

How the Trick Works

The trick is that when the Report is opened by the User, we will check whether the original Report Source Table or Query has any records 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 a single blank record, the Summary Controls will not show 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 i As Integer

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 the Chart Object in VBA
• Linking IBM AS400 Tables