How do I get a COUNT to return a 0 on a report?

[deanstoney]

This is actually related to my last question (about getting a count result to show up by itself on a report). I got the count results to show up like I wanted by adding a subreport with a COUNT function in its footer and then making the rest of the subreport invisible. Now the problem is that I can't get the COUNT function to return 0 when the query it is counting has no records. I'm not using a WHERE clause with the COUNT, because I want it to count all the records in a specified query. I have tried to use IIf, ISNULL, IFNULL, and Nz, and none will work. I created a form with the same data source as a subreport I'm working with, and in the footer of the form I added a text box with this expression: =Nz(Count([EmpID])), 0).
It worked and returned a zero on the form, but the same exact syntax returns nothing or an error on a report built the same way.
Help?

[nico5038]

When you use a DOUNT function like

=NZ(DCOUNT("fieldname","table/queryname","WHERE part") )

you could place the field on your main report.

Nic;o)

[NeoPa]

For anyone interested, the previous question referred to in the OP is actually How do I get a COUNT result to show up by itself on a report?.

[NeoPa]

If you look at my post #7 in the previous (linked) thread you'll see a more natural solution than using subreports, which won't give this problem.

Let us know how you get on.

[deanstoney]

When I try to use the DCOUNT, for some reason then when I try to open the report, it asks for input on the query name, as if it were a parameter and not a query name.

[NeoPa]

Your description is very general.

Perhaps you could fine some relevant code to post and explain how it fits together. From this we may be able to detect what your problem may be (asuming the selected code is relevant of course).

[nico5038]

When I try to use the DCOUNT, for some reason then when I try to open the report, it asks for input on the query name, as if it were a parameter and not a query name.

Are you sure that the DCOUNT is placed in the field with a preceding "=" like:

=NZ(DCOUNT("fieldname","table/queryname","WHERE part") )

Nic;o)

[TheSmileyCoder]

The Query most be enclosed in double quotes, otherwise Access will think your trying to pass it a parameter. What your passing the function is a string literal containing the name of the query/table.

[deanstoney]

Thanks to TheSmileyOne--I feel a little dumb, but I'm kind of a beginner on some of this syntax, and I didn't realize that the double quotes were part of the syntax. With that correction, using :

=Nz(DCount("EmpID", "EMPallUC")

instead of :

=Nz(DCount([EmpID], [EMPallUC])

Nico5038's answer worked well, and I was able to get rid of all of my cumbersome subreports as well! Thank you all!

[NeoPa]

Actually, you need an extra closing parenthesis ")" at the end of each of those lines to make workable code, but I'm sure that's simply a typo.

Glad to hear you have it all working anyway Dean :)