434,916 Members | 1,068 Online
Need help? Post your question and get tips & solutions from a community of 434,916 IT Pros & Developers. It's quick & easy.

# SUM only for - or + fields

 P: 3 Hello, I have tried everthing I can think of but cannot get it to work. I have created a report for Paypal just like a report you receive from the bank every month. I lists all the payments done in one month. On the bottom is the total of the month with =Som([bruto]). (Som because of dutch version). But I also want to see the total of - and + payments. Example: 5.00 10.00 5.00- 5.00 -------- 15.00 Total received: 20.00 Total payed: 5.00 I tried =Som([Bruto]<0) but that only count the payments so it shows 1 in the example and =Som([Bruto]>0) shows 3. I also found onother example here somewhere with dsum but that calculates the sum of all the records in the table. Any idea? Loet Jul 19 '07 #1
5 Replies

 P: 3 Hello, I have tried everthing I can think of but cannot get it to work. I have created a report for Paypal just like a report you receive from the bank every month. I lists all the payments done in one month. On the bottom is the total of the month with =Som([bruto]). (Som because of dutch version). But I also want to see the total of - and + payments. Example: 5.00 10.00 5.00- 5.00 -------- 15.00 Total received: 20.00 Total payed: 5.00 I tried =Som([Bruto]<0) but that only count the payments so it shows 1 in the example and =Som([Bruto]>0) shows 3. I also found onother example here somewhere with dsum but that calculates the sum of all the records in the table. Any idea? Loet Jul 19 '07 #2

 Expert 100+ P: 634 HI Hello, I have tried everthing I can think of but cannot get it to work. I have created a report for Paypal just like a report you receive from the bank every month. I lists all the payments done in one month. On the bottom is the total of the month with =Som([bruto]). (Som because of dutch version). But I also want to see the total of - and + payments. Example: 5.00 10.00 5.00- 5.00 -------- 15.00 Total received: 20.00 Total payed: 5.00 I tried =Som([Bruto]<0) but that only count the payments so it shows 1 in the example and =Som([Bruto]>0) shows 3. I also found onother example here somewhere with dsum but that calculates the sum of all the records in the table. Any idea? Loet From your post I think you need Som(IIF([Bruto]>=0,[Bruto],0) and Som(IIF([Bruto]<0,[Bruto],0) Any good ?? ps if you want payments to +ve then Som(IIF([Bruto]<0,[Bruto]*-1,0) MTB Jul 19 '07 #3

 P: 3 Thanks for the quick response. It does the trick and works OK for me. I only got a syntax error at first because it missed the last ). Loet Jul 19 '07 #4

 Expert Mod 15k+ P: 31,492 You have accidentally posted this question in the Access Articles section. I see you've noticed already so I'll move it to the main Access questions forum. MODERATOR. Jul 19 '07 #5

 Expert Mod 15k+ P: 31,492 Try : Expand|Select|Wrap|Line Numbers =Som(IIf([bruto] < 0, 0, [Bruto])) and =Som(IIf([bruto] < 0, [Bruto], 0)) Jul 19 '07 #6