iif Expression Error

[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

 

[kc-mass]

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

 

[Duane Hookom]

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

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

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

 

[John W. Vinson]

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

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?

 

[zyus]

Change "&" with "And" and it runs successfully

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

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

.

 

[Guest]

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