Results 1 to 6 of 6
  1. #1
    jbarrum is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Apr 2009
    Location
    Houston, Tx
    Posts
    164

    Suming Positive Numbers Only

    Is there a way in Access to create an expression in a report that will sum only the positive numbers in a field?

  2. #2
    CraigDolphin is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Apr 2009
    Location
    Custer, WA, USA
    Posts
    125
    Could you clarify what you mean?

    Given a sequence of
    5
    -2
    3

    should the sum be
    5+3 = 8
    or do you want the sum of the absolute values thusly...
    5 + 2 + 3 = 10

  3. #3
    jbarrum is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Apr 2009
    Location
    Houston, Tx
    Posts
    164
    Quote Originally Posted by CraigDolphin View Post
    Could you clarify what you mean?

    Given a sequence of
    5
    -2
    3

    should the sum be
    5+3 = 8
    or do you want the sum of the absolute values thusly...
    5 + 2 + 3 = 10
    Hello Craig,

    Sorry I was not more clear. I was looking for a way to get the sum of the positive numbers only which would create a result of 8 in your example.

  4. #4
    CraigDolphin is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Apr 2009
    Location
    Custer, WA, USA
    Posts
    125
    Ok, then the idea would be to create a caluclated field in the query which your report is bound to.

    Something like:
    NewField: IIF(Nz([OriginalFieldNameHere],0)<=0,0,[OriginalFieldNameHere])

    This basically says, if the numbers is less than or equal to zero, then use a value of 0. Otherwise, use the value unchanged. And if the field is null, then pretend it's a zero.

    Then simply sum the new field.

  5. #5
    jbarrum is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Apr 2009
    Location
    Houston, Tx
    Posts
    164
    Quote Originally Posted by CraigDolphin View Post
    Ok, then the idea would be to create a caluclated field in the query which your report is bound to.

    Something like:
    NewField: IIF(Nz([OriginalFieldNameHere],0)<=0,0,[OriginalFieldNameHere])

    This basically says, if the numbers is less than or equal to zero, then use a value of 0. Otherwise, use the value unchanged. And if the field is null, then pretend it's a zero.

    Then simply sum the new field.
    Beautiful, That worked perfectly. Thank you very much!

  6. #6
    CraigDolphin is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Apr 2009
    Location
    Custer, WA, USA
    Posts
    125
    Glad I could help

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Sum Of Positive Number and Negative Number
    By maysamab in forum Reports
    Replies: 1
    Last Post: 10-20-2008, 04:06 PM
  2. Non programer needs help in decimal numbers
    By Peter O in forum Access
    Replies: 1
    Last Post: 10-07-2008, 12:29 PM
  3. Matching positive with negative amounts
    By cwert11 in forum Access
    Replies: 1
    Last Post: 09-29-2008, 12:26 AM
  4. Insert Dash between set of Numbers
    By Jhankish in forum Programming
    Replies: 2
    Last Post: 04-19-2007, 08:02 AM
  5. When Week Numbers Collide!
    By Schwagr in forum Queries
    Replies: 3
    Last Post: 03-18-2006, 10:06 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums