Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pulling just Positive numbers from a list (2000)

    I have a column of numbers. Some are positive and some are negative. There are 5 numbers total. I want to take only the positive numbers in that list, add them together and then multiply by 90. I'm trying to do this without VB because I don't know it. I'm using an if statement, but having some trouble. Is there an easy way to ask for just the positive numbers to pull them out? Any help would be greatful. Thanks.
    Deb

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pulling just Positive numbers from a list (2000)

    There are several possibilities. To illustrate them, I'll assume you have a table MyTable with a numeric field MyField.

    If you're doing this in a query, you can calculate the sum in a subquery:

    SumPositive:90*(SELECT SUM(MyField) FROM MyTable WHERE MyField>0)

    or use DSum:

    SumPositive:90*DSum("MyField","MyTable","MyField>0 ")

    If you want to display the sum on a form or report, create a text box with Control Source

    =90*DSum("MyField","MyTable","MyField>0")

  3. #3
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pulling just Positive numbers from a list (2000)

    Thank you for responding and that answers another question I had in another database, but I miss spoke in my earlier message <img src=/S/frown.gif border=0 alt=frown width=15 height=15> . The numbers are not from a single table. They are 5 separate fields, all from a query, but they are separate. Out of those 5 numbers some are pos and neg. I was to pull just the positive numbers, add them together and then multiply by 90.
    Thanks,
    Deb <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pulling just Positive numbers from a list (2000)

    Let's say that the five values are in Field1, Field2, Field3, Field4, and FieldA (for consistency). Use this expression:

    SumPositive:90*(IIf([Field1]>0,[Field1],0)+IIf([Field2]>0,[Field2],0)+IIf([Field3]>0,[Field3],0)+IIf([Field4]>0,[Field4],0)+IIf([FieldA]>0,[FieldA],0))

  5. #5
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pulling just Positive numbers from a list (2000)

    Thank you. I had something similar and modified to your and it works.

    Thanks,
    Deb

Posting Permissions

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