Using the IIF function, the last option is not functional

[Demerit]

In my form footer, I have three four controls

• Gross Amount (Named: "TxtGrossAmount")
• GDiscount (Gross Discount in value e.g $30)
• TDiscount (Discount in percentage form. E.g. 4%)
• Net Payable (Named "TxtNetPayable")

On the control of "NetPayable" I have attached the following IIF function:

=IIf([GDiscount] And [TDiscount] Is Not Null,([TxtGrossAmount]-[GDiscount])*(1-[TDiscount]),IIf([GDiscount] Is Null,[TxtGrossAmount]*(1-[TDiscount]),IIf([TDiscount] Is Null,[TxtGrossAmount]-[GDiscount],[TxtGrossAmount])))

1. The function should test if the controls "GDiscount" and "TDiscount" have a value in them, If yes, it subtracts GrossDiscount from GrossAmount and to the difference, it applies the rate in the control "TDiscount" then returns the final answer. (This works good)
2. Secondly, it test if GDiscount is Null, and If yes, it applies the rate in "TDiscount" and returns the difference. (This works well too)
3. Thirdly, it verifies if "TDiscount" is Null, and If yes, It looks for the difference between "GrossAmount" and "GDiscount" and returns that difference. (this works well)
4. Finally, in the absence of TDiscount and GDiscount, it should return the Gross Amount. (This is were the problem lies)

I dont know why the last condition is not respected.

[June7]

=IIf(Not IsNull([GDiscount] And Not IsNull([TDiscount]), ([TxtGrossAmount]-[GDiscount])*(1-[TDiscount]), IIf(IsNull([GDiscount]), [TxtGrossAmount]*(1-[TDiscount]), IIf(IsNull([TDiscount]), [TxtGrossAmount]-[GDiscount],[TxtGrossAmount])))

Or maybe just:

=([TxtGrossAmount]-Nz([GDiscount],0))*(1-Nz([TDiscount],0))

[Demerit]

Thanks June7
The first one returns an error msg "The expression you entered has a function containing a wrong number of arguments"
but the second one solves my problem.

Just that I don't yet understand how the second function works to produce accurate results, yet so short to write.
If there is a link i can use to learn more about it i will be very grateful.

[June7]

I must have misplaced paren in there somewhere, which I now see is a missing paren: =IIf(Not IsNull([GDiscount])

Search Access and VBA Help for Nz function.

Also review http://allenbrowne.com/casu-12.html

[Demerit]

It still returns a null value for the last condition.

[June7]

Is that for the long version? I don't know why it would unless TxtGrossAmount is null.

But you have solution with the Nz() version?

[Demerit]

Thanks I have the solution with the Nz(), just thought i should let you know.