# Thread: Pulling just Positive numbers from a list (2000)

1. ## 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. ## 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. ## 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. ## 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. ## 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
•