Re. Return a 0 if no records when using =count(*)

[CompassNZ]

Hi Folks,

I use =count(*) to count the records on a report but want to have a more elegant result when there are no records to count. When using =count(*) in this case the user gets a error#. I would like the report to display either a 0 or a message to say something like "There are currently no records to view". I assume I could use an if statement but not sure what the syntax should be or if I can simply write it in the text box or need to write some vba.

Help appreciated!

 

[PaulBricker]

I suppose you could try
=IIf(Count(*)>0,Count(*),"No records returned&quot
in the Control Source of your textbox

Paul

 

[KornGeek]

Another alternative:

=Nz(Count(*),0)

This works similarly to the IIF function, but checks to see if the value is null. It's equivalent to IIF(IsNull(Count(*)),0,Count(*)).