Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Posts
    300

    Unhappy Unanswered: Help with IF Statement

    I am not having much luck with this statement...

    IIF(Tax="No", (([Quantity]*[UnitPrice])*0.065), 0.00)

    Tax is a Yes/No on the report, I keep getting an error that there is one too many ) in the expression..

    What I want is if Tax Exemption=No, then it will calculate the tax, and if it is Yes then it will put 0.00 dollars.

  2. #2
    Join Date
    Jan 2005
    Posts
    144
    Hmmm... I just recreated your scenario and I didn't receive that error. I formatted a text box as currency/2 decimal places and set it's control source to the following:

    =IIf([Tax]=No,(([Quantity]*[UnitPrice])*0.065),0)

    Very nearly what you supplied, I just removed the quotes from "No" and simplified the "0.00" to 0 as the textbox formatting will take care of that for me. The only thing that I can suggest is to double-check your syntax and make sure you don't have any rouge characters floating about. HTH.
    *››DaVinci
    "Simplicity is the ultimate sophistication"

  3. #3
    Join Date
    Jan 2005
    Posts
    144
    Also, I'm quite sure the following syntax will work:

    =IIf([Tax]=No,[Quantity]*[UnitPrice]*0.065,0)

    Completely removing the parentheses from the tax calculation to clean up the code a bit. I haven't tested this, but it should work without complication. If someone else thinks otherwise, please let me know.
    *››DaVinci
    "Simplicity is the ultimate sophistication"

  4. #4
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    And whenever I multiply with fractions I usually round it to however many decimal places I need:

    =IIf([Tax]=No,Round([Quantity]*[UnitPrice]*0.065, 3),0)
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

  5. #5
    Join Date
    Nov 2003
    Posts
    300

    Question

    Thank you! I appreciate all the input!

    It works great for the first condition. The second condition however, I get an *ERROR* on the report. I tried 0 0.00 and both return the same *ERROR*..

    I also checked your suggestion to making sure the Currency 2 digi points was selected and I already did that...

    Any thoughts?

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    try ( ! logic reversed):
    =IIf([Tax],0,Round(nz([Quantity],0)*nz([UnitPrice],0)*0.065, 3))
    and see if it takes you where you want to go.

    i think you will find that Mr Gates' mis-named Yes/No field takes the values True/False (in Access -1/0) rather than No or "No".

    open access and
    Ctrl-G
    and in the immediate window type
    ? True
    hit return
    and then type
    ? No
    hit return

    any difference?

    izy
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •