Use a report to count the # records >0 in Access 2003

[Kay]

Hello all,

I have a query that sums values and throws the answers in the field that it's
value represents. I then put that in a report.

100- 2500 2510- 5000 5001- 70000
$2100.00 $0000.00 $0000.00
$1500.00 $0000.00 $0000.00
$0000.00 $4500.00 $0000.00
$0000.00 $0000.00 $7000.00

Now I need to know how many values are greater than 0 in each field which
would be in the footer of the report i know count is just supposed to count
the total number of records, but is there anyway to base that on a condition?

I tried =iif([100- 2500]>0,Count([100- 2500]),""), but that doesn't ignore
the 0.
Suggestions would be greatly appreciated.

 

[John Spencer]

Count(IIF([100- 2500]>0,1,Null))

OR an alternative
Abs(Sum([100- 2500]>0))

How it works:
[100- 2500]>0 returns True (-1) or False(0) (or Null if the value in the
column in null)
Sum of that expression returns the sum of the -1 , 0 and null values
Abs removes the negative sign.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Kay]

John,

I can't thank you enough. The ABS example worked perfectly.