Access Database

[NJLOAD]

I have a form I use for service calls; I need to calculate money and tax. Some of my clients are tax exempt and I need the tax field to look at a Tax ID field and put a zero amount in this field if the Tax ID field is populated. Here are my formulas on this form at this moment:

Sub Total
Tax: =[Sub Total]*0.07
Total: =[Sub Total]+[Tax]

Is there a formula to add in the Tax field to look at the Tax ID field and put zero?s in there and still add the Sub Total and Total?

Also is there a way to have the Tax field lookup in a table or something the Tax Rate?
As it is now if it changes I have to edit it in the form itself.

Any help is appreciated.

NJLOAD

 

[NeoV]

you can use If statements, like if(TaxID is null, 0,sub-total *rate)

use a seperate table to store the tax codes and rates

 

[NJLOAD]

Thanks NeoV, I'm not program savy and can't get your formula to work. Can anyone else be more specific with the formula?

I've use the Access help file but not much luck there either.

 

[KB]

Not sure if NeoV means the IIF function. If I thought was just a conditional not a function.

http://www.techonthenet.com/ac...tions/advanced/iif.php

IIF([TaxID] = "EXEMPT", 0, [Sub Total]*0.07 )

 

[NJLOAD]

Thanks alot KB, it there a way to modify this statement to work if the Tax ID field is not Null? and only if the word Exempt is in there?

Thanks for all the help.

 

[NJLOAD]

Any one else have any ideas?

 

[ST4RCUTTER]

I would try something like this:

IIf([TaxID] = "Exempt", [Sub Total],[Sub Total] + [Tax])

In this scenario you would have already calculated a field for Sub Total, TaxID, and Tax in separate fields on the same query or in the data source. This If statement only returns the untaxed Sub Total if the status is tax exempt, otherwise it returns thesum of both. A great resource for Access is:

Access World Forums

 

[NJLOAD]

ST4RCUTTER thanks for the reply, How do I make the formula work if the TAX ID field is not NULL?

On the Service form the TAX ID filed is going to be populated with a customer's tax id number and not the word EXEMPT, so I need the formula to work when the field is not NULL, any ideas?

 

[ST4RCUTTER]

If the value is a random number then you could write:

IIf([TaxID] Is Not Null, [Sub Total],[Sub Total] + [Tax])