iif Expression Error

  • Thread starter Thread starter zyus
  • Start date Start date
Z

zyus

Something is not right with my below iif expression where i get this error
"You Can Use Is Operator Only In An Expression With Null Or Not Null"

NEWJArrAmt: IIf([TBL-SKS].[arramt] Is Null &
[TBL-previousmonth].[arramt]=0,[JColl],IIf([TBL-SKS].[arramt]=0 &
[TBL-previousmonth].[arramt]=0,[Jcoll],IIf([TBL-SKS].[arramt] Is Null &
[TBL-previousmonth].[arramt]>0,[TBL-previousmonth].[arramt],IIf([TBL-SKS].[arramt]=0
&
[TBL-previousmonth].[arramt]>0,[TBL-previousmonth].[arramt],[TBL-SKS].[arramt]))))

Help please

Thanks
 
Try it this way:

NEWJArrAmt: IIf(IsNull([TBL-SKS].[arramt]) &
[TBL-previousmonth].[arramt]=0,[JColl],IIf([TBL-SKS].[arramt]=0 &
[TBL-previousmonth].[arramt]=0,[Jcoll],IIf(IsNull([TBL-SKS].[arramt]) &
[TBL-previousmonth].[arramt]>0,[TBL-previousmonth].[arramt],IIf([TBL-SKS].[arramt]=0
&
[TBL-previousmonth].[arramt]>0,[TBL-previousmonth].[arramt],[TBL-SKS].[arramt]))))

Regards

Kevin
 
I don't even want to begin sorting out nested IIf()s. IMO, this type of
calculation belongs in a public function where you can add comments and
re-use it over and over as needed. Save the module as "modBusinessCalcs".

BTW: If you really can stand all the IIf()s, consider replacing all the &s
with ANDs.

--
Duane Hookom
MS Access MVP


kc-mass said:
Try it this way:

NEWJArrAmt: IIf(IsNull([TBL-SKS].[arramt]) &
[TBL-previousmonth].[arramt]=0,[JColl],IIf([TBL-SKS].[arramt]=0 &
[TBL-previousmonth].[arramt]=0,[Jcoll],IIf(IsNull([TBL-SKS].[arramt]) &
[TBL-previousmonth].[arramt]>0,[TBL-previousmonth].[arramt],IIf([TBL-SKS].[arramt]=0
&
[TBL-previousmonth].[arramt]>0,[TBL-previousmonth].[arramt],[TBL-SKS].[arramt]))))

Regards

Kevin



zyus said:
Something is not right with my below iif expression where i get this
error
"You Can Use Is Operator Only In An Expression With Null Or Not Null"

NEWJArrAmt: IIf([TBL-SKS].[arramt] Is Null &
[TBL-previousmonth].[arramt]=0,[JColl],IIf([TBL-SKS].[arramt]=0 &
[TBL-previousmonth].[arramt]=0,[Jcoll],IIf([TBL-SKS].[arramt] Is Null &
[TBL-previousmonth].[arramt]>0,[TBL-previousmonth].[arramt],IIf([TBL-SKS].[arramt]=0
&
[TBL-previousmonth].[arramt]>0,[TBL-previousmonth].[arramt],[TBL-SKS].[arramt]))))

Help please

Thanks
Click to expand...
Click to expand...
 
Something is not right with my below iif expression where i get this error
"You Can Use Is Operator Only In An Expression With Null Or Not Null"

NEWJArrAmt: IIf([TBL-SKS].[arramt] Is Null &
[TBL-previousmonth].[arramt]=0,[JColl],IIf([TBL-SKS].[arramt]=0 &
[TBL-previousmonth].[arramt]=0,[Jcoll],IIf([TBL-SKS].[arramt] Is Null &
[TBL-previousmonth].[arramt]>0,[TBL-previousmonth].[arramt],IIf([TBL-SKS].[arramt]=0
&
[TBL-previousmonth].[arramt]>0,[TBL-previousmonth].[arramt],[TBL-SKS].[arramt]))))

Help please

Thanks
Click to expand...

I agree with Duane - nested IIF gets complex, hard to read and inefficient -
but the problem that's causing this error message is that you're using the
concatenation operator & rather than the SQL conjunction AND. Replace your
ampersands by the word AND - see if that doesn't make it better.

You also need a FALSE branch for your last IIF. Could you describe in words,
or a little table, what result you want for the different combinations of the
two arramt fields?
 
Change "&" with "And" and it runs successfully

Thanks

kc-mass said:
Try it this way:

NEWJArrAmt: IIf(IsNull([TBL-SKS].[arramt]) &
[TBL-previousmonth].[arramt]=0,[JColl],IIf([TBL-SKS].[arramt]=0 &
[TBL-previousmonth].[arramt]=0,[Jcoll],IIf(IsNull([TBL-SKS].[arramt]) &
[TBL-previousmonth].[arramt]>0,[TBL-previousmonth].[arramt],IIf([TBL-SKS].[arramt]=0
&
[TBL-previousmonth].[arramt]>0,[TBL-previousmonth].[arramt],[TBL-SKS].[arramt]))))

Regards

Kevin



zyus said:
Something is not right with my below iif expression where i get this error
"You Can Use Is Operator Only In An Expression With Null Or Not Null"

NEWJArrAmt: IIf([TBL-SKS].[arramt] Is Null &
[TBL-previousmonth].[arramt]=0,[JColl],IIf([TBL-SKS].[arramt]=0 &
[TBL-previousmonth].[arramt]=0,[Jcoll],IIf([TBL-SKS].[arramt] Is Null &
[TBL-previousmonth].[arramt]>0,[TBL-previousmonth].[arramt],IIf([TBL-SKS].[arramt]=0
&
[TBL-previousmonth].[arramt]>0,[TBL-previousmonth].[arramt],[TBL-SKS].[arramt]))))

Help please

Thanks
Click to expand...


.
Click to expand...
 
zyus said:
Something is not right with my below iif expression where i get this error
"You Can Use Is Operator Only In An Expression With Null Or Not Null"

NEWJArrAmt: IIf([TBL-SKS].[arramt] Is Null &
[TBL-previousmonth].[arramt]=0,[JColl],IIf([TBL-SKS].[arramt]=0 &
[TBL-previousmonth].[arramt]=0,[Jcoll],IIf([TBL-SKS].[arramt] Is Null &
[TBL-previousmonth].[arramt]>0,[TBL-previousmonth].[arramt],IIf([TBL-SKS].[arramt]=0
&
[TBL-previousmonth].[arramt]>0,[TBL-previousmonth].[arramt],[TBL-SKS].[arramt]))))

Help please

Thanks
Click to expand...
 
Back
Top