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

How to apply filter to count in report

AccessIdiot
100+
P: 493
This seems like it should be really easy and I think I'm just missing something really dumb.

I have a form that filters a report. At the top of the report I have a bunch of unbound text boxes that do simple things like count and sum. When I apply the filter to the report the text boxes update beautifully when their control source is something like
Expand|Select|Wrap|Line Numbers
  1. =sum([num hatched])
However now I want to apply a clause. If I do this:
Expand|Select|Wrap|Line Numbers
  1. =dcount("[num hatched]", "[tbl_nesting]","[num hatched]>0")
then I get a number but it's ALWAYS that number despite the filter. I want it just to count what's displayed after the filter but
Expand|Select|Wrap|Line Numbers
  1. =dcount([num hatched]>0) 
doesn't work, just reports a false count.

What am I doing wrong?
Jul 13 '10 #1

✓ answered by AccessIdiot

Got it. I needed an IIF statement.

Expand|Select|Wrap|Line Numbers
  1. =Count(IIf([Num Hatched]>0,0))

Share this Question
Share on Google+
7 Replies


NeoPa
Expert Mod 15k+
P: 31,761
There's quite a difference between the Aggregate functions and the Domain Aggregate functions.

In a report a Domain Aggregate function (DCount()) is self-contained. You must specify the record source etc. With an Aggregate function (Sum()) it refers to controls accessible (generally in the Detail section) in your report. Not fields notice (which are references to the underlying data source), but the controls.
Jul 13 '10 #2

NeoPa
Expert Mod 15k+
P: 31,761
You also mention filtering the report. As far as I'm aware, filtering is always applied to the recordsource, and is never effected by the values in controls. Does this help to understand?
Jul 13 '10 #3

AccessIdiot
100+
P: 493
Kind of. I had figured out that Count was going to work better than DCount, but I can't figure out how to phrase the statement I want with the rule applied (e.g. count, which works, vs. count where field value > 0, which doesn't work). Can you apply a rule like that in an aggregate function? I am useless with the expression builder - it is not very intuitive to me.
Jul 14 '10 #4

AccessIdiot
100+
P: 493
Got it. I needed an IIF statement.

Expand|Select|Wrap|Line Numbers
  1. =Count(IIf([Num Hatched]>0,0))
Jul 14 '10 #5

NeoPa
Expert Mod 15k+
P: 31,761
AccessIdiot:
Expand|Select|Wrap|Line Numbers
  1. =Count(IIf([Num Hatched]>0,0))
You did, but I suggest one more like :
Expand|Select|Wrap|Line Numbers
  1. =Count(IIf([Num Hatched]>0,1,0))
Jul 14 '10 #6

AccessIdiot
100+
P: 493
Can you translate that into a full sentence for me? I didn't fully understand even in the one that worked for me why 0 was used for "true" and not 1. In my binary world we always use 0 for false an 1 for true.

So my own example would read:
"If num hatched > 0, then false" which obviously isn't the case. And I'd read your statement to read:
"If num hatched > 0, then true, else false."

So why do the 0's and 1's get switched around? Of is mine just shorthand where the first/only comma is the else statement? Did I just answer my own question lol?
Jul 14 '10 #7

AccessIdiot
100+
P: 493
Actually A your statement didn't work? Now I'm doubly confused.
Jul 14 '10 #8

Post your reply

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