IIf statement with calculation

[hockeyfan21]

Hi there, can't seem to figure this out. I am getting all zero's in this query calculation. Both Ord Entr Qty and OM Rjct Qty are number data types (Long Integer).

Rjct Qty: IIf([Rjct Sched Rsn Cd] In ('Z4','Z5','Z6','Z7','Z8'),[Ord Entr Qty]-[OM Rjct Qty],0)

Without the calculation it works to just show OM Rjct Qty

Rjct Qty: Sum(IIf([Rjct Sched Rsn Cd] In ('Z4','Z5','Z6','Z7','Z8'),[OM Rjct Qty],0))

Any ideas on what I might be doing wrong?

Thanks!

 

[pr2-eugin]

Can you just show one quick example of data??

 

[Trevor G]

Do you have default values of 0 in all fields or are there fields that show no number if so then your formula would to account for that by using either Nz or IsNull

IIF(IsNull(Sum[Rjct Sched Rsn Cd] In ('Z4','Z5','Z6','Z7','Z8'),[OM Rjct Qty],0))

But as requested a screenshot of some sample data or a stripped down version of the database will help find you a solution.

 

[hockeyfan21]

Thanks for the quick responses!

After pulling in just the Ord Entr Qty and matching it to the Rjct Qty, I just realized that my calculation is actually working - I should be getting all zero values! Not what I was expecting but at least I now know the formula is correct.

Thank you so much for helping me troubleshoot this, I really appreciate it!