Sum two fields - IIf statment applies to one

[thart21]

Hi, I am trying to Sum two fields in my query with an IIf statement that only applies to one of the fields. I keep getting syntax errors and can't figure it out.

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

Want to Sum OM Rjct Qty IIf it = the Rjct Sched Rsn Cd then add that total to Uncnfm Qty.

Thanks for any help with this!

Toni

 

[PHV]

Perhaps this ?
Calc Rjct Qty: Sum(IIf([Rjct Sched Rsn Cd] In ('Z4','Z5','Z6','Z7','Z8'),[OM Rjct Qty],0))
Or this ?
Calc Rjct Qty: Sum(IIf([Rjct Sched Rsn Cd] In ('Z4','Z5','Z6','Z7','Z8'),[OM Rjct Qty]+[Uncnfm Qty],0))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[thart21]

Thanks PHV,

My problem is that I want the Uncnfm Qty regardless of what the Rjct Sched Rsn Cd is. Unfortunately, it is applying the IIf statement to OM Rjct Qty AND Uncnfm (for which I need to see add'l Rjct Rsn Codes of Z1,Z2, Z3, etc.) I tried using an IsNull in case it didn't like the OM Rjct Qty being zero but that didn't make a difference.

Thanks for you help, appreciate it1

Toni

 

[PHV]

Like this ?
Calc Rjct Qty: Sum(IIf([Rjct Sched Rsn Cd] In ('Z4','Z5','Z6','Z7','Z8'),[OM Rjct Qty]+[Uncnfm Qty],[Uncnfm Qty]))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[thart21]

That's it! YES! I was missing the 'else' piece to get Uncnfm Qty - thank you so much!