Results 1 to 4 of 4

Thread: IFF Statement

  1. #1
    Join Date
    Jan 2008
    Posts
    5

    Unanswered: IFF Statement

    Hi guys, I'm trying to use the following iif statement in a query and it doesn't seem to be working.

    VAT: IIf([Charge]="NO","0",([Rate]/100*[Sales]))

    "Charge" is a field in one of my tables (also used in the query)

    "Rate" is also a field in on the of the tables.

    "Sales" however is a calculated field in the query (E.g. "Sales: (calculations)

    Maybe this is where I am going wrong? However, even with the 'Charge' field set to "NO" it will display the result "0".

    Thanks for any help.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    What is the data type of Charge? If it's Yes/No, try this:

    VAT: IIf([Charge]=0,"0",([Rate]/100*[Sales]))
    Paul

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    with the 'Charge' field set to "NO" it will display the result "0".
    So how is it not working then? Do you get some error message? The expression says you should get zero for "NO".
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Apr 2009
    Posts
    14
    Quote Originally Posted by Benn
    Hi guys, I'm trying to use the following iif statement in a query and it doesn't seem to be working.

    VAT: IIf([Charge]="NO","0",([Rate]/100*[Sales]))

    "Charge" is a field in one of my tables (also used in the query)

    "Rate" is also a field in on the of the tables.

    "Sales" however is a calculated field in the query (E.g. "Sales: (calculations)

    Maybe this is where I am going wrong? However, even with the 'Charge' field set to "NO" it will display the result "0".

    Thanks for any help.

    Hi Benn,

    You didn’t say exactly what was going wrong with your column returned using the IIf statement, but let me tell you what the statement as written is actually doing and maybe you can take it from there.

    The IIf statement has three parts, expression to evaluate, value returned if true, and value returned if false. The value returned If true is a text zero and the value returned if false is a number, numeric format. Maybe the difference in format returned for whether or not charge=”no” is at the root of your problem. Think about it.

    Mike

Posting Permissions

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