Calculated field with two formulas

[lpkorn423]

I am using a query to organize some data for invoices. Each record has an Invoice Total and a Paid field.

The Paid field is a lookup field with 3 options: "Yes", "Discounted", and Null. This describes three situations: yes means they paid in full, discounted means they paid early at a discounted rate, and Null indicates no payment.



What I would like to do is have a calculated field in the query called TotalPaid. When Paid = Yes, then TotalPaid = InvoiceTotal. When Paid = Discounted, then TotalPaid = InvoiceTotal*.98. When Paid = Null, then TotalPaid = Null (or zero, either option will work)

Can anyone point me in the right direction? Google and forum searches aren't helping but I'm pretty sure I am using the wrong keywords.

[pbaldy]

Check out the Switch() function, or you can nest IIf() functions.

[lpkorn423]

Thanks again pbaldy!

[pbaldy]

No problemo!

[lpkorn423]

Would this be the correct Switch function?

InvoiceAmountPaid: Switch([Paid]="Yes",[InvoiceTotal],[Paid]="Discounted",[InvoiceTotal]*0.98,[Paid]=Null,Null)

[pbaldy]

If it works, yes. If not, no.

That said, nothing is ever "=Null". I'd use IsNull() there.