1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access Formula

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

Thread Status:
Not open for further replies.
Advertisement
  1. jemy

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

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

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

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

    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%. :(
     
  6. OBP

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

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

    jemy Thread Starter

    Joined:
    Dec 21, 2010
    Messages:
    4
    Perfect!!! Thank you so much! (by the way what does Abs stand for?)
    (y):p
     
  9. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Absolute, ie. just the value no minus sign.
     
  10. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/969820

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice