How to count a subset of records?

[naw2]

I have a report with bird nests and number of chicks. I created a calculated control that counts the number of total nests. The control source is =(Count([FledgeNumber])). When I run, I get:

Year TerritoryName FledgeNumber
1996 Nest1 2
1996 Nest2 0
Total Number of Active Nests For 1996 2

I also want to create a calculated control that counts only the number of successful nests (all with FledgeNumber > 0). In the above example, it should yield "1". I'm new to Access, but I've tried everything I can think of and nothing works. Can anyone PLEASE help me?

 

[rjoubert]

Check out the DCount function...

DCount("*", "TableName", "[FledgeNumber] > 0")

 

[naw2]

Thanks rjoubert. I tried your suggestion, then tried

=DCount("[FledgeNumber]","Name Of Query Report is Based on","[FledgeNumber]>0")

and

=DCount("[FledgeNumber]","Name Of Query Report is Based on","[FledgeNumber]<>0")

All gave "#Error" in the report. I then created a new query that only included records with > 0 FledgeNumber and re-ran with this:

=DCount("[FledgeNumber]","Name Of New Query Without 0 FledgeNumber")

It didn't work either. I have the queries (and thus, the report) setup to prompt for a year of interest before running, but that didn't interfere with the first calculated control box I added, so I can't see why that would have anything to do with it. I can't figure out why these aren't working. Any other suggestions?

 

[dhookom]

Don't use domain aggreagate functions if you want to count or sum records from your report's record source. You can try add a control to a report or group footer with a control source like:

=Sum(Abs(FledgeNumber<>0))


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]