Calculated fields based on If statement

[pthomp52]

I have a table in a database where I need to make a calculated field for VAT based on the criteria of another field. I have an amount field, a cash/invoice field which is filled from a combo box in a form with only choice Cash or Invoice, and I need the VAT to look at the cash/invoice field and if it says cash then return a zero and if not then calculate 20% of the amount field.
I can't seem to be able to get it to look up what is in the cash/invoice field to do this

[kd2017]

First, don't use actual calculated fields in the table!!! You can achieve this in your queries.


I'd suggest that the cash/invoice field actually be a yes/no field. For example [IsInvoice]. You can still use a combobox in your form.

Behind the scenes in M$Access the Yes = -1 and No = 0. So you could calculate VAT = -0.2*[IsInvoice]*[amount field]

I've attached an example DB demonstrating my method as well as the IIF statement you've asked for.

Also, I use integer fields instead of actual yes/no fields per allen browne's advice here: http://allenbrowne.com/NoYesNo.html

[Welshgasman]

I'd have to disagree in this case, as one would have to look up the VAT rate for that particular date and then apply.?

Depends if your combo is read from a table?
If it was and 1 was Cash and 2 was Invoice, then

Me.txtVAT = IF(Me.Combo = 1,0,Me.txtAmount * Me.CurrentVAT)

Me.CurrentVAT would be the current VAT rate and being used like that now, would be 0.2

If it was a value list, then just check for "Cash" instead

HTH