By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,827 Members | 2,220 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,827 IT Pros & Developers. It's quick & easy.

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

P: 7
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?
Feb 1 '10 #1

✓ answered by nico5038

When you use a DOUNT function like
Expand|Select|Wrap|Line Numbers
  1. =NZ(DCOUNT("fieldname","table/queryname","WHERE part") )
you could place the field on your main report.

Nic;o)

Share this Question
Share on Google+
9 Replies


nico5038
Expert 2.5K+
P: 3,072
When you use a DOUNT function like
Expand|Select|Wrap|Line Numbers
  1. =NZ(DCOUNT("fieldname","table/queryname","WHERE part") )
you could place the field on your main report.

Nic;o)
Feb 2 '10 #2

NeoPa
Expert Mod 15k+
P: 31,299
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?.
Feb 2 '10 #3

NeoPa
Expert Mod 15k+
P: 31,299
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.
Feb 2 '10 #4

P: 7
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.
Feb 2 '10 #5

NeoPa
Expert Mod 15k+
P: 31,299
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).
Feb 2 '10 #6

nico5038
Expert 2.5K+
P: 3,072
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:
Expand|Select|Wrap|Line Numbers
  1. =NZ(DCOUNT("fieldname","table/queryname","WHERE part") )
Nic;o)
Feb 2 '10 #7

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
Feb 2 '10 #8

P: 7
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 :
Expand|Select|Wrap|Line Numbers
  1. =Nz(DCount("EmpID", "EMPallUC")
instead of :
Expand|Select|Wrap|Line Numbers
  1. =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!
Feb 2 '10 #9

NeoPa
Expert Mod 15k+
P: 31,299
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 :)
Feb 3 '10 #10

Post your reply

Sign in to post your reply or Sign up for a free account.