Count query = 0, how to show on report? 3

[petelyons]

Hi,
I'm using a count query that in some cases will return zero; meaning no records found. In report preview, it displays #Error instead of zero. How can I make the report display a zero in this case? Hoping to save time with some canned VBA code if anyone has any. Thanks in advance!
-Pete Lyons

 

[jurgen]

Do you wan't to execute the report, or display an dialog box with a status on it ?

Jurgen

 

[petelyons]

Thanks for replying Jurgen.

It's a simple borderless text box that shows the number of records found based on my Count query. All other text boxes based on other Count queries are showing the number just fine. Only the ones where the Count query returns no (er, null?) records found are showing as #Error instead of 0.

I simply need to know how to handle this case in VBA where a Count query returns nothing (i.e., zero) and show an actual zero in the text box instead of #Error like I'm getting now.

 

[JoeMiller]

Pete,

You can use the IsNull function to return True/False when examining something in Access. Try something like this as the source of your control:

=iif(IsNull([MyField]),"0",[MyField])

HTH Joe Miller
joe.miller@flotech.net

 

[petelyons]

Thanks for the reply Joe. Unfortunately, and for whatever reason, the IsNull function didn't work for my situation; but I appreciate your pointing me in the right direction. I found that the IsError function does the trick:

I first made a hidden textbox as follows:

Data: =Sum([MyDatabaseField]) which sometimes returns nothing
Name: Txt_TTL_Invisible
Visible: No

I then made a visible textbox as follows:

Data: =IIf(IsError([Txt_TTL_Invisible]),"0",[Txt_TTL_Invisible])
Visible: Yes

Thanks again,
Pete Lyons ;-)