Calculating sales tax by purchase date

[Jan Il]

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.

 

[Allen Browne]

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.

 

[Jan Il]

Hi Allen

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.

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... ))

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

 

[Allen Browne]

Hi 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.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen

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.

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... ))

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

 

[Jan Il]

Hi Allen

Hi 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.

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen

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.

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... ))

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

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.

 

[Jan Il]

Hi 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.

Hi 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.

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen

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.

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... ))

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

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.

 

[Allen Browne]

Wow. So many people affected.

The impact will be prolonged for so many.