Is null is not null and then

[Teddy]

I have 3 different formulas and I am looking for help understanding what functions they are performing. I was able to translate each to a point but after that I am going to need some help; can you help me?

[FONT=Times New Roman][SIZE=3]IIf(IsNull(QRY_ReplaceOH_2![New/old 100]) And IsNull([Old/New]) And Not (IsNull(QRY_OnHandSummary!SumOfTotal)),QRY_OnHandSummary!SumOfTotal,IIf(Not (IsNull(QRY_ReplaceOH_2![New/Old 100])),QRY_ReplaceOH_2![New/Old 100],0))[/SIZE][/FONT]

If the New/old 100 field from QRY_ReplaceOH_2 is null and the SumOfTotal field from QRY_OnHandSummary! is not null then.

[I][SIZE=3][FONT=Times New Roman]IIf(IsNull(QRY_ReplaceOH_2![New/Old 200]) And IsNull([Old/New]) And Not (IsNull(QRY_OnHandLocSummary!SumOfTotal)),QRY_OnHandLocSummary!SumOfTotal,IIf(QRY_ReplaceOH_2![New/Old 200]>0,QRY_ReplaceOH_2![New/Old 200],0))[/FONT][/SIZE][/I]

If the New/old 200 field from QRY_ReplaceOH_2 is null and the SumOfTotal field from QRY_OnHandSummary! is not null then.

[FONT=Times New Roman][SIZE=3]IIf(IsNull(QRY_ReplaceOH_2![New/Old Hai]) And IsNull([Old/New]) And Not (IsNull(QRY_SummaryOnHandHai!CountOfSer)),QRY_SummaryOnHandCebu!CountOfSerial,IIf(QRY_ReplaceOH_2![New/Old Hai]>0,QRY_ReplaceOH_2![New/Old Hai],0))[/SIZE][/FONT]

If New/Old Hai field from QRY_ReplaceOH_2 is null and the CountOfSer field from QRY_SummaryOnHandHai is not null then..

 

[Brianwarnock]

It can be difficult to trace the logic of IIF statements it can be useful to break them into pseudo Block If format at the "," which indicate the True and False parts , so

IIf(IsNull(QRY_ReplaceOH_2![New/old 100]) And IsNull([Old/New]) And Not (IsNull(QRY_OnHandSummary!SumOfTotal)),QRY_OnHandSummary!SumOfTotal,IIf(Not (IsNull(QRY_ReplaceOH_2![New/Old 100])),QRY_ReplaceOH_2![New/Old 100],0)

becomes

IIf(IsNull(QRY_ReplaceOH_2![New/old 100]) And IsNull([Old/New]) And Not (IsNull(QRY_OnHandSummary!SumOfTotal)),
Then QRY_OnHandSummary!SumOfTotal,
else IIf(Not (IsNull(QRY_ReplaceOH_2![New/Old 100])),
then QRY_ReplaceOH_2![New/Old 100]
else ,0)

Brian