IIF function

[NVAlice]

=IIf([Bond Cost]-[BndPymntSubform].[Form]![TotalBndPayment]=Null,[Bond Cost],[Bond Cost]-[BndPymntSubform].[Form]![TotalBndPayment])

This expression will not give me the results if the [Bond Cost]-[BndPymntSubform].[Form]![TotalBndPayment]=Null it completes the rest of the operation.

Can anyone tell me why this is happening? I am trying to create this expression on a form.

Thanks,

 

[Mile-O]

You can't equal Null[/i] (= Null) as you can't determine the equality of one thing against an unknown value. Null, again, is an unknown value.

The function you should use to determine if something is null is IsNull().

i.e.

This is what you are attempting.

IsNull([Bond Cost]-[BndPymntSubform].[Form]![TotalBndPayment])

Better, however, would be to evaluate both values using the Nz() function which converts null to zero.

=IIf(Nz([Bond Cost])-Nz([BndPymntSubform].[Form]![TotalBndPayment]) = 0,[Bond Cost],[Bond Cost]-[BndPymntSubform].[Form]![TotalBndPayment])

 

[NVAlice]

Okay, I tried this and it didn't work. I only get the balance due if I put a value in the payment field. It actually worked just like the first formula I did. I tried to post a screen print of the problem but couldn't get it to copy over to this forum. This expression is made up from a form and a subform, does that make any difference?

Thanks for you help this is a great site.

I got it to work! I had to give the "total payment" field a value.

Thanks again for your help.

 

[john471]

I believe the default action of th Nz() function is to return a zero-length-string if the argument is null, which differs from a zero. You can, however, use the nZ() function with the optional parameter for what to return in the event of the main argument being Null, to over-ride the zero-length-string.

e.g. nz(YourField,0)

In your case:-
=IIf(Nz([Bond Cost],0)-Nz([BndPymntSubform].[Form]![TotalBndPayment],0) = 0,[Bond Cost],[Bond Cost]-[BndPymntSubform].[Form]![TotalBndPayment])

I assume that since:-
number - Null ==> Null
Your reason for your first test was in case TotalBndPayment is Null ?

Further assume that [Bond Cost] is intended to always be number ?

The result you are looking for is [Bond Cost] - [TotalBndPayment], whilst treating Null the same a zero ???

Therefore:-
You don't neeed the IIF, you can just use :
=Nz([Bond Cost],0)-Nz([BndPymntSubform].[Form]![TotalBndPayment],0)

 

[NVAlice]

Thank you John 471. That seemed to work the best.

NVAlice

 

[john471]

Glad it helped.

Thanks for replying back :- That often helps others too, to know what solution worked when they do a search and find a thread with similar problem.

Regards

John.