Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2012
    Posts
    68

    Unanswered: iif expression not working the way i want it to

    Here's the expression:

    507 Totals After Tax: IIf([Items for Job Transfer]![class]="1" And [items for job transfer]![tax status]="t",Nz([quantity])*Nz([unit price])*1.06,Nz([quantity])*Nz([unit price]))

    "class" field consists of 2 numbers. 1 and 2
    What im after:

    if "class" = 1 and "tax status" = t then qty*price*1.06
    if "class" = 1 and "tax status" = t/e then qty*price

    This would be one field
    The other would be

    if "class" = 2 and "tax status" = t then qty*price*weight*1.06
    if "class" = 2 and "tax status" = t/e the qty*price*weight

    i'm placing the expression into a query if it matters. Thanks in advance for help.

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    if "class" = 1 and "tax status" = t then qty*price*1.06
    if "class" = 1 and "tax status" = t/e then qty*price

    This would be one field
    The other would be

    if "class" = 2 and "tax status" = t then qty*price*weight*1.06
    if "class" = 2 and "tax status" = t/e the qty*price*weight
    1-Are Class 1 and Class 2 in two seperate fields, or two possible values for the same field?
    2-What's the difference whether the class is 1 or 2? The formula is the same.
    3-Is class a string variable in the table or is it an integer? I notice you put "" marks around it ("1") in your statement.

    To the point: if class 1 and 2 have the same formula, why put it in the iif() condition in the first place?
    Also, even if classes 1 and 2 have different formulas (not like in your posting), you can put it all into one complex iif() statement, as follows:
    Code:
    507 Totals After Tax: IIf([Items for Job Transfer]![class]="1", IIf([items for job transfer]![tax status]="t",Nz([quantity])*Nz([unit price])*1.06,Nz([quantity])*Nz([unit price]),<follow by the formula for class 2 using the same iif() format as before, iif(items for job transfer]![tax status]="t", etc) and close with an additional right parenthese)
    Sam

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,475
    Provided Answers: 11
    If I was you I would create a function in a module then what I need returning
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  4. #4
    Join Date
    Mar 2012
    Posts
    68
    Quote Originally Posted by sam
    1-Are Class 1 and Class 2 in two seperate fields, or two possible values for the same field?
    2 possible values for the same field.

    Quote Originally Posted by sam
    2-What's the difference whether the class is 1 or 2? The formula is the same.
    The formula is not the same. Value 2 needs to calculate unit weight where value 1 has no unit weight.

    Quote Originally Posted by sam
    3-Is class a string variable in the table or is it an integer? I notice you put "" marks around it ("1") in your statement.
    I'm new access and not quite sure what the difference is yet between an integer or string variable. 1 (or 2) represent the PK from tblClass. Knowing what I do I would have to guess a string variable.

    Im going to try your suggestion about keeping it all one complex expression. I will repost the expression if I still have issues

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    put tax rates into a separate table NOT hardcoded into queries
    it will bite you in the backside
    when tax rates change its a user action (they own their data they chaneg their data so it doesn't require any developer action. usually you will be the last to be told of such a change and testing the change can take a long while.

    I'd be tempted to flatten the process and set a weight of 1 so in effect instead of 4 separate equations you end up with 1, possibly two if you cannot set weight to 1 for class 1 items
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Mar 2012
    Posts
    68
    Heal,
    I'll take your advice on the tax. Read that somewhere else too. This was kind of a temp solution for now. Also changing the weight to 1 may work. Thanks for the idea. Now I have to work on writing that code/expression

  7. #7
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Good. Now you know why healdem is a moderator on the forum. He really has a lot to teach.

    Also, if class 1 or 2 is an autonumber, it is not a string but a numerical type Long. Hence, it does not get quotes around it.

    Sam

Posting Permissions

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