conditional count in access report

[haneen97]

Hi,

Trying to count the number of record in a report that has a certain value in a field and it counts all the record in the report. Is there a way to count do a conditional count in a report?

Thanks

Mo

 

[mp9]

The trick is to use the condition you want to evaluate in a SUM() function rather than a COUNT().

Let's say there's the field you want to check on your report is called ActiveFlag and that it is either true (-1) or false (0).

In the report footer add a text box with the following control source:

=Sum(-1*([ActiveFlag]=-1))

This works in the following way:
1. First, evaluate ([ActiveFlag]=-1) - this returns True if the condition is true, otherwise False. Since true and false equate to numerical values (-1 and 0 respectively) the result of this evaluation can be used in a sum.
2. Since you don't want to count negative numbers, multiply the result from step one by -1, so (-1*([ActiveFlag]=-1)) returns 1 or 0.
3. Add these values up using Sum(). Since all records that return zero won't contribute to the sum, you'll get a count of all records that match your condition.

Here are some other examples of how to use this approach:

A. To only count where the numerical NumberOfOrders field = 0

=Sum(-1*([NumberOfOrders]=0))

B. To only count where the text Status field = "A"

=Sum(-1*([Status]="A"))

http://www22.brinkster.com/accessory/