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

  • Thread starter Thread starter Kay
  • Start date Start date
K

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.
 
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
 
Back
Top