Thread: Counting values greater than zero (2000)

1. Counting values greater than zero (2000)

We are having a banquet where people are paying various prices, entered in the field called Package. Some are not coming to the banquet, so their cost is zero, also entered in the field called Package. I have to tell the caterer how many people are coming to the banquet. This means counting up the values in the field Package that are greater than zero. I tried to do this ... =IIf([Package]>0,Count([Package]>0)) ... but the result was the number of entries in the field, Package, including those with zero.

What am I doing wrong?

2. Re: Counting values greater than zero (2000)

Count doesn't work that way, it counts all non-null values. You can use

=DCount("*","NameOfTheTable","Package > 0")

where NameOfTheTable is the name of the table, or

=Abs(Sum([Package]>0))

3. Re: Counting values greater than zero (2000)

Thanks. I used the second formula and it worked like a charm. If you have time, could you explain what the formula is asking Access to do.

4. Re: Counting values greater than zero (2000)

The expression [Package]>0 results in True if Package is positive, and in False otherwise. True is stored internally as the number -1 and False as 0.

The expression Sum([Package]>0) adds these values; each positive value of Package contributes -1, so if there are 10 records with positive Package, the sum results in -10.

The Abs (absolute value) function removes the sign from a number: Abs(12) = 12, Abs(0) = 0 and Abs(-10) = 10. So Abs(Sum([Package]>0)) results in the number of records with positive Package.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•