By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,916 Members | 1,068 Online
Bytes IT Community
+ Ask a Question
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
Share this Question
Share on Google+
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

NeoPa
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

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

Post your reply

Sign in to post your reply or Sign up for a free account.