Not charging Sales Tax if Customer has Resale Number ??

[mthornblad]

Hi

I have a Master/Detail (form/subform) for Customer Invoices. The
Master portion of the form contains the Customer info. Customer
number, customer name, resale number, and invoice number. Some
customers have a resale number and some don't.

The Detail portion of the form contains the line items for the
invoice. One of the fields in each line items is sales tax.

If the customer has a resale number, then they are not charged sales
tax. If the customer does not have a resale number, they are charged
the current sales tax rate.

How can I handle this situation ? I would appreciate any help.

Thanks in advance

Mark

 

[John W. Vinson]

Hi

I have a Master/Detail (form/subform) for Customer Invoices. The
Master portion of the form contains the Customer info. Customer
number, customer name, resale number, and invoice number. Some
customers have a resale number and some don't.

The Detail portion of the form contains the line items for the
invoice. One of the fields in each line items is sales tax.

If the customer has a resale number, then they are not charged sales
tax. If the customer does not have a resale number, they are charged
the current sales tax rate.

How can I handle this situation ? I would appreciate any help.

Thanks in advance

Mark

How are you calculating the tax now?

You could use an expression in a calculated control on the subform:

[ItemPrice] * (1 + IIF(IsNull(Parent![ResaleNumber]), [TaxRate], 0)


John W. Vinson [MVP]

 

[Allen Browne]

Use the BeforeInsert event procedure of the subform to look up the
customer's [Resale Number] and assign the TaxRate accordingly.

Ideally the line items table has a TaxRate field, not a TaxAmount field.
This avoids the dependency between the fields (always good design.)

Form_BeforeInsert fires only when creating a new record. Using this event
prevents the problems that could occur with editing old records after a
customer acquired (or lost) their tax free status.

The lookup could look in a hidden column of the CustomerID combo on the main
form. Alternatively, it could use DLookup() to examine the record in the
Customer table. For help with DLookup() see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

 

[mthornblad]

Use the BeforeInsert event procedure of the subform to look up the
customer's [Resale Number] and assign the TaxRate accordingly.

Ideally the line items table has a TaxRate field, not a TaxAmount field.
This avoids the dependency between the fields (always good design.)

Form_BeforeInsert fires only when creating a new record. Using this event
prevents the problems that could occur with editing old records after a
customer acquired (or lost) their tax free status.

The lookup could look in a hidden column of the CustomerID combo on the main
form. Alternatively, it could use DLookup() to examine the record in the
Customer table. For help with DLookup() see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

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

I have a Master/Detail (form/subform) for Customer Invoices. The
Master portion of the form contains the Customer info. Customer
number, customer name, resale number, and invoice number. Some
customers have a resale number and some don't.

The Detail portion of the form contains the line items for the
invoice. One of the fields in each line items is sales tax.

If the customer has a resale number, then they are not charged sales
tax. If the customer does not have a resale number, they are charged
the current sales tax rate.

How can I handle this situation ? I would appreciate any help.

Thanks in advance

Mark

Allen.. Thanks .. I have the current tax rate hidden on the subform.
I will store the tax rate as opposed to the tax amount in the
underlying table as you suggest. As far as future updating of these
records, I have the Data Entry property set to Yes so users can only
enter new data. John's suggestion is basically what I was looking
for. I am going to try it and hopefully it will work.

This is my first project with Access. I have many years of
programming experience with Cobol. But Access is creating a bit of a
learning curve. But I am learning SQL and Access and am really
enjoying the challenge. The more I learn the more I like it.

I will have many more questions as I develop the system I am working
on and look forward to your help in the future.

Your expertise is greatly appreciated ...

Mark

 

[mthornblad]

Hi

I have a Master/Detail (form/subform) for Customer Invoices. The
Master portion of the form contains the Customer info. Customer
number, customer name, resale number, and invoice number. Some
customers have a resale number and some don't.

The Detail portion of the form contains the line items for the
invoice. One of the fields in each line items is sales tax.

If the customer has a resale number, then they are not charged sales
tax. If the customer does not have a resale number, they are charged
the current sales tax rate.

How can I handle this situation ? I would appreciate any help.

Thanks in advance

Mark

How are you calculating the tax now?

You could use an expression in a calculated control on the subform:

[ItemPrice] * (1 + IIF(IsNull(Parent![ResaleNumber]), [TaxRate], 0)

John W. Vinson [MVP]

Thanks John

When you say "calculated control" on the subform, I hope you mean the
Tax control. Your 1 line solution is exactly what I was looking for.
I will give this bit of code a go and hopefully I will get it right.
Thanks again....

Mark