Count on report producing error if null value!! 1

[Syerston]

I have a report that produces a count in a text box for the number of records returned.

However, if the count produces a null value #error is thrown up.

The report is called from a command button on a form.

Any guidance would be much appreciated. B-)

 

[balor]

How do you count the records? If you do it in the query be sure that you make the count on a field that can't be NULL. For example the primary key.

 

[Syerston]

Sorry I should have been more specific.

There are two additional text boxes on the form(dialog box)prompting for start and end dates. Quite often the report will return no records for the search, but when it does the report and page header appear along with #error where the count should be.

The expression to count the records is on the report in a text box, =Count(*) together with leading and trailing text.

Management require a nill report to be produced if there are no records.

The primary key in this case is [ProjectNo].

 

[balor]

OK then i understand! If there are no records to return the COUNT(*) return #error?

Check if there are any records before you use the count on the textbox.

Example:
=iif(IsNull([ProjectNo]);"0";Count(*))

 

[balor]

One more thing! The ProjectNo have to be a bound textbox in the report else you can't access it with the IsNull function. If you don't want to show the value you can hide the textbox (Visible = False).

 

[Syerston]

Many Thanks, it worked a treat