# Thread: Field Calculation based on Conditions

1. Registered User
Join Date
Nov 2011
Posts
4

## Unanswered: Field Calculation based on Conditions

Hi all,

I've got small issue with a calculation on an Access module I am working on a the moment.

The layout is something along the lines

Cost / RetailPrice / Qty / Tax1 / Tax2

Tax1 and Tax2 are Yes/No fields

Basically I need to multiply
RetailPrice by Qty by 1.1 if Tax1 is Yes or by 1.2 if Tax2 is Yes

Also some fields may not have either Tax1 or Tax2 ticked.

I can do the first part as in "Value: [RetailPrice]*[Qty]" but setting the criteria for the additional maths is proving frustrating at the moment.

I hope I have explained the problem clearly. Any and all help, advice or pointers greatly appreciated.

2. Registered User
Join Date
Oct 2009
Posts
204
Try this:
Iif([Tax1]=True,([RetailPrice]*1.1),(Iif([Tax2]=True,([RetailPrice]*1.2),[RetailPrice])))

3. Registered User
Join Date
Nov 2011
Posts
4
Thank you, will give it a try in the morning, and let you know how it goes.

Just one more quick question, I presume I put that in to the sql code after the WHERE statement?

Cheers

4. Registered User
Join Date
Oct 2009
Posts
204
I'm an SQL idiot but I did a mockup and here's how it worked

SELECT Table1.RetailPrice, Table1.Tax1, Table1.Tax2, IIf([Tax1]=True,([RetailPrice]*1.1),(IIf([Tax2]=True,([RetailPrice]*1.2),[RetailPrice]))) AS test
FROM Table1;

I named a new field "Test" to return your calculation.

5. Registered User
Join Date
Nov 2011
Posts
4
Thank you chris, just put this to the test and it appears to be exactly what I need.

Join Date
Feb 2004
Location
New Zealand
Posts
1,482
What about putting 1.1 in tax1 and 1.2 in tax2
Because if the tax changes you don't have to change the formula

7. Registered User
Join Date
Nov 2011
Posts
4
Hi myle, the db I'm working with is the back end of an ePOS system, and the tax rates are actually stored in another table, called TaxRates.

The Tax1 and Tax2 Fields in the table I'm linking to and running the query from are Yes/No Fields, and in the VB Frontend the calculations are run automatically, and the values are stored in the Receipts table.

Do you think it would be possible to call in the Tax1 and Tax2 fields from the TaxRates table and have the calculation run based on their values?

If so, can you please give me an example.

There is no rush in this as chris's post has resolved the issue for now, but I'm always interested in learning a bit more.

Thanking you.

8. Registered User
Join Date
Oct 2009
Posts
204
Try This

SELECT Table1.RetailPrice, Table1.Tax1, Table1.Tax2, IIf([Table1].[Tax1]=True,([RetailPrice]*[TaxRates].[Tax1]),(IIf([Table1].[Tax2]=True,([RetailPrice]*[TaxRates].[Tax2]),[RetailPrice]))) AS Total
FROM Table1, TaxRates;

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•