Change tax, but leave old records alone

[Amelia]

Hi, I have invoices that I would like to leave the old tax rate so the
invoice totals stay the same. So how do I change the tax rate with out
affecting all of my invoices? Could I go from a certain date?

Currently I have a query and in that query I have this in my tax field:

Tax: IIf([tax exempt]=True,0,[subtotal]*0.065)

Is there a way to tell it if the invoice date is (less than) >7/1/09 to
*[subtotal] by .065 and if it is (greater then) <7/1/09 to *[subtotal] by
..0875?

(Also let me know if I have my greater/less then signs the wrong way. I
think I might for coding.
Thanks!

 

[Rick A.B.]

Hi, I have invoices that I would like to leave the old tax rate so the
invoice totals stay the same. So how do I change the tax rate with out
affecting all of my invoices? Could I go from a certain date?

Currently I have a query and in that query I have this in my tax field:

Tax: IIf([tax exempt]=True,0,[subtotal]*0.065)

Is there a way to tell it if the invoice date is (less than) >7/1/09 to
*[subtotal] by .065 and if it is (greater then) <7/1/09 to *[subtotal] by
.0875?

(Also let me know if I have my greater/less then signs the wrong way. I
think I might for coding.
Thanks!

Amelia,

Normally you would store the tax rate in the invoices table so that
each invoice would record the tax rate used for that transaction.

Hope that helps
Rick

 

[Paolo]

Hi Amelia,
This > is greater than and that < is less than. For your question it's 'nuff
to add another iif statement i.e.

IIf([tax exempt]=True,0,iif([invoice
date]>#07/01/2009;[subtotal]*0.0875;[subtotal]*0.065))

In this way if the invoice date is 07/01/2009 you have [subtotal]*0.065

IIf([tax exempt]=True,0,iif([invoice
date]>=#07/01/2009;[subtotal]*0.0875;[subtotal]*0.065))
In this way if the invoice date is 07/01/2009 you have [subtotal]*0.0875

HTH Paolo

 

[vanderghast]

Someone can use a table for tax rates:

TaxeRates
Rate, FromDate, ToDate ' fields
0.07 2000.01.01 2006.07.01
0.065 2006.07.01 2008.05.01
0.08 2008.05.01 null



and then, you can always compute the applicable tax amount, given the
billingDate:

SELECT b.amount * t.rate
FROM bills AS b INNER JOIN taxesRates AS t
ON b.billingDate >= t.FromDate AND b.billingDate < Nz(t.ToDate,
#01/01/3000#)



Vanderghast, Access MVP