Problem with Sum

  • Thread starter Thread starter Inma
  • Start date Start date
I

Inma

Hello,

Under Details I list three types of agents' commissions from query as COM_A,
COM_B and COM_C.

But if there're no commissions to pay I only list the field when it has a
quantity. In other words:

ColumnA =IIF([COM_A]>'0',[COM_A],'')

ColumnB =IIF([COM_B]>'0',[COM_B],'')

and ColumnC =IIF([COM_C]>'0',[COM_C],'').

At the Foot of Report I sum the 3 columns as Total Commissions:

=Sum([COM_A])+Sum([COM_B])+Sum([COM_C])

However, the following happens: when COM_B or COM_C have no values, Total
Commissions has no value even though COM_A has a value. In other words, Sum
is not summing.

Why is it?

TIA

Inma
 
You might need to use Nz()
=Sum(Nz([COM_A],0))+Sum(Nz([COM_B],0))+Sum(Nz([COM_C],0))

Also, if the COM fields are numeric, change your expressions to something
like:
ColumnA =IIF([COM_A]>0,[COM_A],Null)
or
ColumnA =IIF([COM_A]>0,[COM_A],0)
 
Back
Top