Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154

    Unanswered: Reporting SUM() Question

    Hey, I've been trying to find this on the boards but couldn't find it:

    I have a report where a section header has the following items:

    [SumOfAmt1] [SumOfAmt2] sum([sumofamt1]-[sumofamt2])

    When I preview the report, I only get the third column when the first two columns are populated... For example, if both columns were populated, it would appear as such:

    1,500 1,200 300

    However, if one of the two colums is blank, I get nada in the third column, as such:

    1,500 (blank) (blank)
    (blank) 1,200 (blank)

    Any ideas on how I can fix this in the report?

    Thanks,

    mk

  2. #2
    Join Date
    Sep 2004
    Location
    Raleigh, NC
    Posts
    146
    Will something like the following do the trick?

    [SumOfAmt1] [SumOfAmt2] sum(NZ([sumofamt1])-NZ([sumofamt2]))

    or maybe something like:

    [SumOfAmt1] [SumOfAmt2] sum(IIF(IsNull([sumofamt1]),0,[sumofamt1])-IIF(IsNull([sumofamt2]),0,[sumofamt2]))
    Last edited by smacdonaldnc; 12-20-04 at 20:49.

  3. #3
    Join Date
    Dec 2004
    Location
    Netherlands
    Posts
    11
    "[SumOfAmt1] [SumOfAmt2] sum([sumofamt1]-[sumofamt2])"

    is the word SUM nessecary there???? you can just leave it away i think:
    "[SumOfAmt1], [SumOfAmt2], ([sumofamt1]-[sumofamt2]) as Difference"

    But don't forget the ( )

  4. #4
    Join Date
    Sep 2004
    Location
    Tampa, FL
    Posts
    520
    Just to add,n case you did not know (though you may) any time you have a "Blank" or a Null value in any calculation the result will out as Null. Unless you account for the possibility.

    My 2 Cents
    Darasen

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by Darasen
    Just to add,n case you did not know (though you may) any time you have a "Blank" or a Null value in any calculation the result will out as Null. Unless you account for the possibility.

    My 2 Cents
    Damn, beaten like a red-headed stepchild caught behind the couch with his cousin.

    but yeah, the problem is x - null = null

    That's because null is an unknown or non-existant value. If you take an unknown value and add/subtract anything from it, the result is still unknown, or null.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154

    Thumbs up That's it!

    SMacdonaldNC, that nailed it! Oooh, sometimes I truly impress myself with my inability to look for the simple solutions...

    Thanks, everybody.

Posting Permissions

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