IIF Function in form

Question

Hello everyone!

i am currently building a data base to track sales for my small company. Some people pay taxes and others do not. I am trying to use the IIF function to to automatically charge or not charge taxes.

I have [Invoice Total] which is the sum of all items that will be pruchased. I have [checkBox 23] which is a Yes/No check box which indicates if the customer payes taxes.

This is the how I wrote out the function but seems to not work:

=IIF( [CheckBox23]="Yes", "[Invoice Total]*0.07","0")

I am not too familiar with access and I wrote out the above formula after watching a few videos on youtube and reading different examples online.

Please help!

Thank you!

Answer 1

Personally, I would do it different.

The customers table would have a yes/no field ChargeTax. It may change at some point.

The Invoices table woule have a field TaxPct, required entry.

When the invoice is created, lookup whether a customer is to be charged tax.

If yes, populate TaxPct with the current tax percent. If no, populate with zero.

Tax changes over time and if you hard code it into a formula, then change it, that changes it for ALL invoices.

Then in a query, based on the invoice, add a column for TaxAmout = InvoiceTotal * TaxPct.

You can then use the query as the recordsource for your form, as well as statements, invoice reports, etc., and don't need to duplicate the formula anywhere else.

Answer 2

That's possibly because the actual field is a boolean (Yes/No, or true/false), not a text field. The underlying values for booleans are -1 for "yes" or "true" and 0 for "no" or "false". Try checking for -1, instead of "yes".

Answer 3

Thank you!

I treid with this, but I get "0" even when the check box is yes :/.

I appreciate you help.

Answer 4

Although I have some concerns about the overall strategy, this ought to create the desired calculation for the tax amount:

=[Invoice Total] * IIF([CheckBox23]="Yes", .07,0)

Two comments on naming.

It's generally better to create object names with no spaces in them. {InvoiceTotal] instead of [Invoice Total]

Leaving the default names provided by Access tends to make maintenance harder. Maybe, chkTaxable, instead of CheckBox23 would be clearer when revisiting this form in the future.