Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
  •