# Access Formula

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

#### jemy

Thread Starter
I designed a Invoice database. I need to be able to have it calculate the following: if the invoice is over \$5,000 then tax at 7%. If the invoice is under \$5,000 tax at 6% plus \$50. If the invoice is a negative amount then it needs to deduct the same tax as was originally charged. Expr1: (IIf([Amount]<-5000,[Amount]*0.07,IIf([Amount]>-5000,[Amount]*0.06+50,[Amount]*0.07)) this is the formula I came up with, but it does not do the negative amounts correctly. Specifically if the negative amount is like -5020.

#### OBP

jemy, welcome to the Forum.
I am not sure I understand your requirement, why subtract the Tax and then add it back on for negative values, why not just ignore them all together?
Are you doing this in a Query, on a form or in a report?

#### jemy

Thread Starter
This is a large County/City Project where the vendors are not subjected to taxes. But the City-County wants to know how much it saved in taxes on the projectby doing it this way Say we got an invoice for \$7000. and the tax was 490. Sometime later we were given a credit memo for this amount my formula looks at it like it is less than 5,000 and does the 470 instead. There are thousands of invoices and a lot of vendors. This needs to be automated. I did the formula in a several queries because I use it on different reports

#### OBP

Are you sure that this is what you have?
Expr1: (IIf([Amount]<-5000,[Amount]*0.07,IIf([Amount]>-5000,[Amount]*0.06+50,[Amount]*0.07))
As it is only testing the negative values and anything greater than -5000 includes all plus values as well.
I would have thought that it would be something like
Expr1: (IIf([Amount]>5000,[Amount]*0.07,IIf([Amount]>0 and [Amount]<=5000,[Amount]*0.06+50))

Can you lay out the actual requirements in ordinary english for me?
Like
less than 0 tax at x%
0 to 5000 tax at 6% plus \$50
Over 5000 tax at 7%

#### jemy

Thread Starter
Tax -5,000 to \$5,000 tax at .06% plus \$50
greater than \$5,000 or less than -\$5,000 tax at .07%

The problem stems from any negative amount less than -\$5,000. It keeps using the .06 +50 instead of the .07%.

#### OBP

OK, this seems to do what you want, I tested it with 5001, 501, -501 & -5001.

tax: IIf([Amount]>5000 Or [Amount]<-5000,[Amount]*0.07,IIf([Amount]>=-5000 And [Amount]<=5000,[Amount]*0.06+50))

PS. Except the 50 gets added to the negative value for 501 tax calc.

#### OBP

This is better

tax: IIf([Amount]>5000 Or [Amount]<-5000,Abs([Amount]*0.07),IIf([Amount]>=-5000 And [Amount]<=5000,Abs([Amount]*0.06)+50))

#### jemy

Thread Starter
Perfect!!! Thank you so much! (by the way what does Abs stand for?)

#### OBP

Absolute, ie. just the value no minus sign.

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

As Seen On

### Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

over 807,865 other people just like you!