Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    3

    Unanswered: Separate subtotals for positive and negative values

    I am a newbie. I'm trying to make MYOB (accounting package) do things it's not designed to do, and extract and process data in ways it doesn't want. I don't have a choice, I have to work with MYOB.

    I have a report in Access of sales that are commissionable. It includes negative values from customers who have gotten refunds. Commission for those sales then need to be deducted from commissions.

    I want a subtotal of all sales (the positive values) and a separate subtotal of all refunds (the negative values). Is there an expression that I can use in the footer that will do this? Or will I need to redo the groupings somehow in my query?

    Any help would be appreciated.

    Kevin
    Last edited by kevrhon; 10-06-03 at 13:17.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Put 2 textboxes in your footer and set the RowSources to : =SUM(IIf([MyAmtHere]<0,[MyAmtHere],0))

    and

    =SUM(IIf([MyAmtHere]>=0,[MyAmtHere],0))


    Where the <0 amt is the Refund and the >=0 amt is sales ...

  3. #3
    Join Date
    Oct 2003
    Posts
    3

    Thumbs up

    Beautiful! Thanks so much. I figured there had to be an easy solution to that, but I couldn't get the syntax right.

    Thanks again.

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Beautiful! Thanks so much. I figured there had to be an easy solution to that, but I couldn't get the syntax right
    How so? All you have to do is plug in your table/query field or editbox name in for MyAmtHere ... Where's the difficulty?

Posting Permissions

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