Nested IIf statement use

[Guest]

Hi All,

I have created a query that Subtotals a row of figures in a table, then adds
the appropriate tax for each location (based on province and country). I am
using the following nested IIf statement:

Sales Tax:
IIf([StoreProvince]="NB",[SubTotal]*0.15,IIf([StoreProvince]="NL",[SubTotal]*0.15,IIf([StoreProvince]="NT",[SubTotal]*0.15,IIf([StoreCountry]="US",0,[SubTotal]*0.07))))

Although I'm sure that there's another way to do this, this works as it is.
Here's the problem: the sales tax rates are changing July 1st. I would like
to continue to show the above rates for payments made prior to July 1st.

Is there an easier way to do this?

Thanks.

 

[John Vinson]

Hi All,

I have created a query that Subtotals a row of figures in a table, then adds
the appropriate tax for each location (based on province and country). I am
using the following nested IIf statement:

Sales Tax:
IIf([StoreProvince]="NB",[SubTotal]*0.15,IIf([StoreProvince]="NL",[SubTotal]*0.15,IIf([StoreProvince]="NT",[SubTotal]*0.15,IIf([StoreCountry]="US",0,[SubTotal]*0.07))))

Although I'm sure that there's another way to do this, this works as it is.
Here's the problem: the sales tax rates are changing July 1st. I would like
to continue to show the above rates for payments made prior to July 1st.

Is there an easier way to do this?

Not sure it's easier, but there are better ways.

I'd suggest a TaxRate table with three fields - StoreProvince,
EffectiveDate, and Rate, with values like

NB;#7/1/2005#;0.15
NB;#7/1/2006#;0.175
NL;#7/1/2005#;0.15

<and so on>

Rather than a hairy nested IIF, which is inefficient and hard to
maintain when rates change, you could join this table in a Query,
joining StoreProvince to StoreProvince and using a criterion on the
EffectiveDate to select the most recent previous rate.


John W. Vinson[MVP]

 

[Guest]

Thanks John. That will probably work. BUT: 1) how do I handle those records
which are US and have no tax? Also, how would I write the criteria for "most
recent"?

Thanks!

Hi All,

I have created a query that Subtotals a row of figures in a table, then adds
the appropriate tax for each location (based on province and country). I am
using the following nested IIf statement:

Sales Tax:
IIf([StoreProvince]="NB",[SubTotal]*0.15,IIf([StoreProvince]="NL",[SubTotal]*0.15,IIf([StoreProvince]="NT",[SubTotal]*0.15,IIf([StoreCountry]="US",0,[SubTotal]*0.07))))

Although I'm sure that there's another way to do this, this works as it is.
Here's the problem: the sales tax rates are changing July 1st. I would like
to continue to show the above rates for payments made prior to July 1st.

Is there an easier way to do this?

Not sure it's easier, but there are better ways.

I'd suggest a TaxRate table with three fields - StoreProvince,
EffectiveDate, and Rate, with values like

NB;#7/1/2005#;0.15
NB;#7/1/2006#;0.175
NL;#7/1/2005#;0.15

<and so on>

Rather than a hairy nested IIF, which is inefficient and hard to
maintain when rates change, you could join this table in a Query,
joining StoreProvince to StoreProvince and using a criterion on the
EffectiveDate to select the most recent previous rate.


John W. Vinson[MVP]

 

[John Vinson]

Thanks John. That will probably work. BUT: 1) how do I handle those records
which are US and have no tax?

Use a Province code of US (hey, I might prefer to be part of a
Canadian province rather than my current government...), or if you
track it, the two-letter US state code, with a value of 0.00 as the
tax rate.

Also, how would I write the criteria for "most
recent"?

A Subquery:

=(SELECT Max([EffectiveDate]) FROM [TaxRates] WHERE
[TaxRates].[EffectiveDate] <= [SaleDate])

John W. Vinson[MVP]

 

[Guest]

John,
I am also in Canada and our GST (goods & services tax) rate has just changed
from 7% to 6%. We also have Provincial tax which is 7% but may change as
well. I have created a table with the fields:
TaxID
TaxType
TaxEffectivedate
TaxRate

I am trying to create a query which will apply the appropriate GST & PST to
line items in our purchase orders. Could you elaborate on your answer to
D.M. about making a subquery? Do you put that statement in the Criteria line
of a calculated field in the query? I expect that I should have one
calculated field for the PST and one for the GST.
Sorry if I am starting a new thread in the middle of another one. I am new
to Access and new to this Discussion Group. It just seemed that your answer
to D.M. almost got me the knowledge that I need.
Thanks!

Thanks John. That will probably work. BUT: 1) how do I handle those records
which are US and have no tax?

Use a Province code of US (hey, I might prefer to be part of a
Canadian province rather than my current government...), or if you
track it, the two-letter US state code, with a value of 0.00 as the
tax rate.

Also, how would I write the criteria for "most
recent"?

A Subquery:

=(SELECT Max([EffectiveDate]) FROM [TaxRates] WHERE
[TaxRates].[EffectiveDate] <= [SaleDate])

John W. Vinson[MVP]