IIF Problems

[suverman]

SELECT L4, L3, L5, IIf([sum(DR)]>[sum(CR)],[sum(DR)]-[sum(CR)],0), IIf([sum(CR)]>[sum(DR)],[sum(CR)]-[sum(DR)],0)


FROM GL
WHERE L4 not in (0)
GROUP BY L4, L3, L5;

This is the code that isnt working, asking for parameters of "sum(dr)".


What i basically want is, to net the fields DR and CR first and then summation it according to codes. Thank you.

[suverman]

select L4, L3, L5, sum(NET_DR), sum(NET_CR)

from [

SELECT L4, L3, L5, IIf([DR]>[CR],[DR]-[CR],0) NET_DR, IIf([CR]>[DR],[CR]-[DR],0) NET_CR;
FROM T_RB
WHERE L4 not in (0)
GROUP BY L4, L3, L5, IIf([DR]>[CR],[DR]-[CR],0), IIf([CR]>[DR],[CR]-[DR],0)

]

GROUP BY L4, L3, L5.


I tried this too, says invalid bracketing of name.

[orange]

In my view the

[sum(DR)]

(andothers) is being treated as a field name.
In Access field names containing special characters or space must be enclosed in square brackets [..].
Are DR, CR columns in table T_RB?

Can you describe in plain English what calculation and what result you want to display?

[suverman]

So what it is,

I have lot of records with fields CODE, DR and CR.
There can be same codes in different records.
Firstly, need to sum all the individual codes sum(DR), sum(CR)
Now, the summed sum(DR) and sum(CR) for each codes need to be net.
NET(DR) field = if sum(dr) > sum (cr), sum(dr) - sum(cr), 0
NET(CR) field = if sum(cr) > sum (dr), sum(cr) - sum(cr), 0


In the end, the result should be individual codes with total net dr / net credit