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
Joined
Dec 21, 2010
Messages
4
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

Joined
Mar 8, 2005
Messages
19,895
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
Joined
Dec 21, 2010
Messages
4
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

Joined
Mar 8, 2005
Messages
19,895
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
Joined
Dec 21, 2010
Messages
4
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

Joined
Mar 8, 2005
Messages
19,895
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

Joined
Mar 8, 2005
Messages
19,895
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
Joined
Dec 21, 2010
Messages
4
Perfect!!! Thank you so much! (by the way what does Abs stand for?)
(y):p
 
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.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
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.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top