Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Jul 2003
    Location
    Notre Dame, Indiana, USA
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Beyond Iif expressions.. (Access 2003)

    I have a query that uses Iif expressions to return one of two choices. I now need to be able to return one of THREE choices. I realized this is beyond the capabilities of the Iif expression. What other choices do I have? I do not use VBA, but if someone would walk me through the process in tiny baby steps, I am willing to try it.

    Thanks!

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Beyond Iif expressions.. (Access 2003)

    This is not beyond the IIF function, you can have quite a few nests. You can set it up similar the the example below. You must just modify the fields to your own:

    =IIF([Field1]<=500,"Very Bad",IIF([Field1]<=1000,"Better",IIF([Field1]<=2000,"Very Good","Give him a raise!!")))

    The first IIF tests the value (or another field value). If it is NOT less than 500, it moves to the next IIF and tests the logic, etc.

    If you can give more detail on your database fields, we can assist you to build up the expression if the above example is not clear enough.
    Regards,
    Rudi

  3. #3
    Lounger
    Join Date
    Jul 2003
    Location
    Notre Dame, Indiana, USA
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Beyond Iif expressions.. (Access 2003)

    I may not have given enough info before. My expression is actually creating a new field as well as evaluating the weight of the paper we used, ike this:

    PaperWt: IIf([PaperType]=1,"30lb","45lb")

    My "PaperType" choices are now 1= 30lb, 2=45lb and 3=27.7lb. My expression is in the 'field' area of the Access query, not the 'criteria' area.

    If you need more info let me know. I do so appreciate the help.

  4. #4
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Beyond Iif expressions.. (Access 2003)

    Rudi gave you the basic structure, you just need to adapt it to your situation.

    IIf([PaperType]=1,"30lb",IIF([PaperType]=2,"45lb","27.7#"))


    iif(test,true,iif(test if first test is false,second test true,result if test 1 and 2 are false))

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Beyond Iif expressions.. (Access 2003)

    mbarron has provided the necessary formula, or you can add a third argument if there is no PaperType, like this:

    PaperWt: IIf([PaperType]=1,"30lb",IIf([PaperType]=2,"45lb",IIf([PaperType]=3,"27.7lb","No paper type provided")))
    Regards,
    Rudi

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Beyond Iif expressions.. (Access 2003)

    Another option is
    <code>
    PaperWt: Choose([PaperType],"30lb","45lb","27.7lb")
    </code>
    Choose looks at the value of the first argument, and if it is 1, returns the first of the list of values following it, and if it is 2, the second, etc.

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Beyond Iif expressions.. (Access 2003)

    In addition to the Choose() function that Hans mentioned, there is also a Switch() function that will handle what you have. You can find both in Access Visual Basic Help.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Beyond Iif expressions.. (Access 2003)

    I took the liberty of finding this web page that gives some good examples of the Switch Function.

    TX Mark and Hans,
    I remember the Choose Function, but forgot about it, and,
    I never knew about the Switch Function...totally new!!
    Regards,
    Rudi

  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Beyond Iif expressions.. (Access 2003)

    I would do it a different way again. Just create a Paper types table, and join that to the query.

    That way you can maintain the data and add new values without having to delve into the workings of the query.


    <table border=1><td>1</td><td>30 lb</td><td>2</td><td>45 lb</td><td>3</td><td>27.7 lb</td>
    </table>
    Regards
    John



  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Beyond Iif expressions.. (Access 2003)

    If there's a chance that the list will grow, that would definitely be the way to go. Much easier to maintain.

Posting Permissions

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