Coditional IIf with an AND/OR statement
I think your best and easiest method is to create a RateTable of criteria and
tax rate like this --
Prov RateStart RateEnd Rate
ON 6/30/2010 0.0415
BC 6/30/2010 0.049
ON 5/10/2010 6/30/2010 0.0415
BC 4/23/2010 6/30/2010 0.049
And then this IIF statement ---
IIF([YourTable].[Prov] = [RateTable].[Prov] AND [YourTable].[Date of
Service] Between [RateTable].[RateStart] AND Nz([RateTable].[RateEnd],
#12/31/9999#)
--
Build a little, test a little.
"theref147" wrote:
Trying to get a query to determine a tax rate based on the province on a
record.
It should look at the [Prov] field and based on what is in that field come
up with the corresponding tax rate. eg IIf ([Prov]= "ON" and [Date of
Service]2010/06/30,.13,.05, OR IIf ([Prov]="BC" and [Date of
Service]2010/06/30,.12,.05) or something of this nature. I am not sure how
to write the formula could someone help.
|