Calculating sales tax by purchase date

Calculating sales tax by purchase date

Post by Jan I » Mon, 29 Aug 2005 06:53:26


Hi all :-) - Access 2003 - XP Pro

I need to be able to display the current sales tax on a form, by the
purchase date, times the current sales tax % to accommodate the expected
increase in state sales tax next year.

Example:

If purchase date is 01/01/05 thru 12/31/05 = 5%
If purchase date is 01/01/06 thru 12/31/06 = 5.5%

The current tax percentage will applied against the unit cost in another
control to provide the sales tax amount to be displayed, and the total cost
will be the sum of the Unit cost, sales tax amount and shipping and any
handling costs.

Would it be best to do set the criteria for the calculations in the query,
or in the form control itself. If so, what type of expression or code would
I need to calculate the tax amount?

Jan :)
MS MVP - IE/OE
Smiles are meant to be shared,
that's why they're so contagious.
 
 
 

Calculating sales tax by purchase date

Post by Allen Brow » Mon, 29 Aug 2005 13:12:47

Jan, add a field to this table to hold the tax rate: Number, size Double,
format Percent.

If you could ever need to cope with some items of an invoice being
tax-exempt, you will want to do this in the detail table. You will then use
a query as the RecordSource of your form, with a calculated field such as
this:
Tax: [Quantity] * [UnitPrice] * [TaxRate]

You can use an Update query to populate the new field with 0.05% for all
existing entries.

You can store the TaxRate in a table, DLookup() the current rate in the Open
event of the form, and assign to the Default Value of the text box.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://www.yqcomputer.com/
Reply to group, rather than allenbrowne at mvps dot org.

 
 
 

Calculating sales tax by purchase date

Post by Jan I » Mon, 29 Aug 2005 22:06:01

i Allen :-)


Thanks Allen, I'll give this a try. But, it does not appear to check for the
date of purchase. For now, the rate of 5% would apply, however, after
01/01/06 the tax rate will increase to 5.5%. The problem being, there may
be invoices for purchases in 2005 that won't come in until after the 1st of
Jan. 06. If the sales tax calculated is increased to 5.5%, then the entries
for the purchases prior to Jan. 1st will be automatically calculated by the
5.5%. Or perhaps I am just not seeing the process clearly enough.

The idea behind this process is that, there may be several different parts
on an invoice or receipt and sometimes sales tax is paid at the POP, but,
there is only one total sales tax amount listed. Each item on the list
would have to be entered separately and the sales tax calculated for each
part individually. There is a check box at the start of the entry the User
will click if sales tax was paid, and this will activate the sales tax being
calculated or not. The appropriate sales tax should then be calculated
against the purchase price of each item entered, based upon the date of
purchase. Since there could be purchases may not be entered until after the
new tax rate would take effect, the tax is then calculated based upon the
date of purchase as well. So, it should check the PurchaseDate field to find
out what tax rate should apply for that entry.

The process would work the same for the POS, so that the new tax rate would
be calculated automatically based upon the date of sale and the total sale
amount.

I currently have a table with the sales tax amounts as below, but, not if
needed,

EffectiveDate Tax Rate
01/01/05 5%
01/01/06 5.5%

The key to whether or not sales tax is calculated on either the POP and POS,
is determined by checking the sales tax box. So if the sales box is
checked, the tax rate to be calculated would be determined by the date of
purchase. I may be missing something in process.

I hope I am explaining clearly enough. I know how difficult it is to try to
see inside the posters head when your crystal ball is still in the
cleaners... :o))

Jan :)
MS MVP - IE/OE
Smiles are meant to be shared,
that's why they're so contagious.


 
 
 

Calculating sales tax by purchase date

Post by Allen Brow » Mon, 29 Aug 2005 23:03:09

i Jan.

The crucial thing is to *store* the tax rate in the table, at the time the
entry is made. Then if the tax rate changes later, all existing entries are
correct, any new ones get the new rate, and so everything is correct. I'm
assuming you have a main table (like Northwind's Order table) and a related
table (like Order Details.) My preference is to put the TaxRate in the
detail table, because it copes with more situations, but where it is will be
up to you.

Now, if you need the software to lookup the TaxRate depending on the
document date, you would create a tax rate table, with fields:
StartDate The first date this value applies from.
TaxRate The rate to use from that date onwards.

Now you can get the tax rate to use in a record from the Extended DLookup()
function from here:
http://allenbrowne.com/ser-42.html

The code will end up something like this:
strWhere = "StartDate <= " & Format([PurchaseDate], "\#mm\/dd\/yyyy\#")"
Me.TaxRate = ELookup("TaxRate", "tblTaxRate", strWhere, "[StartDate]
DESC")

Perhaps that function was the main thing you were after, since DLookup() is
not really up to the task.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jan Il" < XXXX@XXXXX.COM > wrote in message
news:% XXXX@XXXXX.COM ...


 
 
 

Calculating sales tax by purchase date

Post by Jan I » Tue, 30 Aug 2005 00:24:23

i Allen :-)


Yes...the ELookup does look as it will give the results we need. I already
have the tblTaxRate set up with the necessary information so can I use that
one.

I'll give this a try and see how it works. I may not be able to get to it
for a day or so, as I have a new beginners class to conduct and need to
prepare for on short notice, so I will not be able to work on this right
away. If you don't mind, I'd appreciate it if you would check back in a day
or so. <s>


Jan :)
MS MVP - IE/OE
Smiles are meant to be shared,
that's why they're so contagious.




 
 
 

Calculating sales tax by purchase date

Post by Jan I » Tue, 06 Sep 2005 04:20:37

i Allen! :-)

Sorry for the prolong delay, urgent matters related to family in the Katrina
storm zones, but, happily, all are accounted for, although, a bit roughed up
around the edges.


I think this will work, but, need to know if this would do into one of the
Events of the control, or if it needs a Function code as it appears from the
information at the website info you posted for me. Just not sure.

Thank you! :-)

Jan :)




 
 
 

Calculating sales tax by purchase date

Post by Allen Brow » Tue, 06 Sep 2005 10:11:00

Wow. So many people affected.

The impact will be prolonged for so many.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://www.yqcomputer.com/
Reply to group, rather than allenbrowne at mvps dot org.