IIF Statement Confusion

[ncalcaterra]

Hi there, i wonder if anyone can help shed some light on where i might be going wrong with this formula that i'm using in one of my reports... the field names are listed below:

txtAFee1Total
CmpdF2P
CmpdF2LS

Heres where i'm at now: =IIf(nz([CmpdF2LS],0)=0 and nz([CmpdF2P],0)=0,0,IIf(nz([CmpdF2LS],0)=0,[CmpdF2P]*[txtAFee1Total],[CmpdF2LS]))

In plain english though, i want "0" to appear if both CmpdF2P and CmpdF2LS are null - otherwise, if CmpdF2LS is null then i want to multiply txtAFee1Total by CmpdF2P. I should also mention that there can not be a value in both CmpdF2P and CmpdF2LS at the same time, but they can both definitely be empty.

Thanks so much if you can help find my error here - it's makin' me crazy!

 

[Darrylles]

Hi,

Try this:

iif (isnull(cmpdf2p) and isnull(cmpdf2ls),0,iif(isnull(cmpdf2ls),txtAFee1Total * CmpF2P,0))

The fact that there can not be a value in both CmpdF2P and CmpdF2LS at the same time should be ensured by your data logic - not tested by queries that require info.
i.e. A query COULD test for this, but this would simply mean that you are allowing illegal data-entry. Better to stop this, than continually check for it throughout your application.

ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk