How to apply filter to count in report

[AccessIdiot]

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

=sum([num hatched])

However now I want to apply a clause. If I do this:

=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

=dcount([num hatched]>0)

doesn't work, just reports a false count.

What am I doing wrong?

[NeoPa]

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.

[NeoPa]

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?

[AccessIdiot]

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.

[AccessIdiot]

Got it. I needed an IIF statement.

=Count(IIf([Num Hatched]>0,0))

[NeoPa]

AccessIdiot:

=Count(IIf([Num Hatched]>0,0))

You did, but I suggest one more like :

=Count(IIf([Num Hatched]>0,1,0))

[AccessIdiot]

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?

[AccessIdiot]

Actually A your statement didn't work? Now I'm doubly confused.