counting items on report

[dd/mm/yy]

hello

i have a report that shows closed orders from a query i have, and in the report footer i have a total field with the syntax Count([OrderID]) so that every record in the query is counted to show a total. however if there are no closed orders the field shows #Error and obviously i would rather it just say "0".

i've tried using IIf(Count([OrderID])=0,0,Count([OrderID])) - if counted items = 0, display 0, if not display the actual counted number, but that failed miserably as well.

i assume the idea of using IIf was right, but im stil a newb at this database game!

incidentally, anyone know what the extra I infront of IIf means? just wondering....

dd/mm/yy

 

[RichO]

I've been scratching my head on this one as well and I'm hoping one of the forum experts can give their solution.

It appears that you are trying to count a field that has no value. I've tried...

=Count(*)

...but that still gives the same #Error if there are no records returned by the query.

 

[dd/mm/yy]

nice to know im not the only one!

as you say hopefully an expert will be able to clear this up for us.

 

[pbaldy]

Try this:

=IIf(IsNumeric([OrderID]),Count([OrderID]),0)

 

[dd/mm/yy]

ahah! just the ticket! i had wondered about trying if IsNull() but wasnt sure whether it was actually null or an error code or watever - thanks anyway!

 

[RichO]

It seems that no matter how much you've already learned there is always something more to learn. Never heard of IsNumeric before but it's good to know.