How to use an IF Statement in Access

G

Guest

Hi, The question I have is how to use an IF Statement with Access.
a. Using access I created a table made of 3 columns
1. ItemNum
2. Cost
3. Quantity
From this table I created a Query with these additional fields
1. SubTotal: [Quantity]*[ItemCost]
2. Tax: [SubTotal]*0.0825
3. Total: [SubTotal]+[Tax]
From this Query I create a Form – on this form I place a Radio button – If
the Radio button is NOT checked, the item is taxable – Then add tax to the
total. - If it is check, the item is not taxable, do not add tax.
That is it.

I am open to any suggestion such as using Radio button or check box, or …
In the query which develops the form and report, I do the calculation.

Please advice,
Tony
 
A

Allen Browne

Hi Tony

If some items are taxable, and others are not, I suggest you add another
column to your able:
4. TaxRate
The field type will be Number, size Double, format Percent.

Now add it as a text box in your form.
Set its Default Value to 0.0825
For non-taxable items, enter zero in that row.

In query design, type this expression into the Field row:
Tax: CCur(Round(Nz([Quantity] * [ItemCost] * [TaxRate],0),2))

By storing the tax rate instead of a yes/no (radio button), your design not
only copes with tax-free items, but will also work correctly for all
existing records when they change the tax rate at some time in the future,
or if the software ends up being used in a place that has a different tax
rate.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top