Calculated cell calculation option required

  • Thread starter Thread starter Bruce
  • Start date Start date
B

Bruce

Hi,

I have the following calculated field on my form which works as long as my
records contain [Trans_Type_ID]= 1 AND 2.

=Sum(IIf([Trans_Type_ID]=1,[Quantity]))-Sum(IIf([Trans_Type_ID]=2,[Quantity]))

Some of my data only contains records with [Trans_Type_ID]= 1 (i.e. no
instances of 2. In the absense of [Trans_Type_ID]= 2 I would like it to
calculate for 1 only.

i.e. Sum(IIf([Trans_Type_ID]=1,[Quantity]))

How can I modify my code to allow for this situation.

Regards,

Bruce
 
Try thi
=Sum(IIf([Trans_Type_ID]=1,[Quantity]))-Nz(Sum(IIf([Trans_Type_ID]=2,[Quantity])),0)
 
Back
Top