# Access Formula

Discussion in 'Business Applications' started by jemy, Dec 21, 2010.

Not open for further replies.
1. 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.

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

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

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

5. 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%. 6. 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.

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

8. Perfect!!! Thank you so much! (by the way what does Abs stand for?)  9. Absolute, ie. just the value no minus sign.