Hiding error messages
This tip looks at hiding error messages in a report, specifically the #error.
Note: This tip is only required for Microsoft Access 2003 or earlier, as Microsoft Access 2007, 2010, automatically displays a blank field if there is no data, so no need for the following work around. Which is great.
The #Error message in a report occurs when there are no underlying records in the table or query the report is based on (i.e. an empty recordset).
The most likely field this will happen to is an unbound field that performs a calculation on another field, i.e. Sum, Average or Count. If there are no records to perform the calculation on then the #Error message is displayed.
If you have many calculated unbound controls (fields), you will need to do this on each field. The reason for this is if the report comes accross one error, it stops calculating all the other fields and displays #error in all of them. So repeat the below for all calculated fields that may have a record count of 0.
First you have to decided what you wish to display when hiding error messages (#error) You may with to display a 0, or leave the field blank if there is no data, as it makes the report a bit tidier, easier to read and more professional.
One way of hiding the error message is to check whether the report has any data in it or not by evaluating the HasData property and then combining it with the IIF function. The HasData property returns one of three values:
-1 = Bound report with records
0 = Bound report with no records
1 = Unbound report
The example below shows how to use this in an unbound text box on a report that sums the field [Number1] in the report [Report1]. If there is data the sum of the field [Number1] is displayed, otherwise 0 is displayed.
To set this up normally you would, add an unbound text box to the report and add the following syntax, usually in the header or footer:
=Sum([Number1])
But if the field Number1 or some reason had no data you would just get the following diplayed.
#error
Therefore change the above to:
=IIf([Report].[HasData],Sum([Number1]),0)
This will overcome this problem.
If you would like to learn more about the IIF function, you may wish to check out the following:
IIf function - an introduction
An example of an IIF function and conditional formatting.
