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,422
    Provided Answers: 8
    If I was you I would create a function in a module then what I need returning
    hope this help

    See clear as mud


    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 based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  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
  •