none
replacement for IIF statement? RRS feed

  • Question

  • Hello everyone,

    I've got 1 table with the following field names:(shortened version for explanation
    ProductID           primary key. this is no autonumber but looks like this: 170700101
    ProductGroup      calculated field: Right([ProductID];2) the last 2 digits of the productid field defines the product group
    ProductName      calculated field: IIf([ProductGroup]="01";"Product number 1";"unknown")

    Basicly this table decides what the productname will be, based on these 3 fields. Its works great! except i can only use 14 iif statements while i need like 40. I need a new solution for this but im kinda new to access and have no idea what to do now.

    Is there anyone who can help me?

    excuse my bad english!

    Danny

         

    Sunday, July 9, 2017 7:40 PM

Answers

  • Create a table with ProductGroup as primary key and ProductName as second field.

    Instead of using a calculated field, you can create a query based on your table and the new table, joined on ProductGroup. Return the fields from your table, plus ProductName from the new table.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Dannyzuidh Tuesday, July 11, 2017 7:37 PM
    Sunday, July 9, 2017 7:50 PM

All replies

  • Create a table with ProductGroup as primary key and ProductName as second field.

    Instead of using a calculated field, you can create a query based on your table and the new table, joined on ProductGroup. Return the fields from your table, plus ProductName from the new table.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Dannyzuidh Tuesday, July 11, 2017 7:37 PM
    Sunday, July 9, 2017 7:50 PM
  • Hi Dannyzuidh,

    it try to make a test on my side and I find that we can add 14 iif condition maximum in expression.

    so to solve the issue you can try to use nested iif condition may solve your issue.

    if that not solve your issue then you can try to use 'Switch' instead of 'iif'.

    but I find that you cannot use 'switch' directly in the expression of table.

    so you need to create query for that in which you can use 'switch' and then you can use a temporary table and move your data to that temporary table for further processing.

    Output:

    Reference:

    Switch Function

    Regards

    Deepak 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, July 10, 2017 2:21 AM
    Moderator
  • You could also write a function and use the case statement.
    Monday, July 10, 2017 2:23 PM
  • Create a table with ProductGroup as primary key and ProductName as second field.

    Instead of using a calculated field, you can create a query based on your table and the new table, joined on ProductGroup. Return the fields from your table, plus ProductName from the new table.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    I did create the table and joined them, but I have no idea how to return the data to the table?
    Monday, July 10, 2017 7:30 PM
  • You don't have to nest them all into 1 calculated field.  Separate them into a set of individual calculated fields - and then typically you have 1 final calculated field that ties them together.

    Monday, July 10, 2017 9:41 PM
  • You don't have to return the data to the table. You can create a query based on the two tables, as described in my previous reply.

    Use that query as record source for forms and reports, instead of the original table.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, July 10, 2017 10:23 PM
  • Hi Dannyzuidh,

    you had mentioned that,"I did create the table and joined them, but I have no idea how to return the data to the table?"

    if you try to follow my approach and try to create query as I suggested before then you can easily return that data to table.

    you just need to open query in design view then you need to click on 'Make Table' button under 'Query Type' group in 'Design' Tab.

    then click on run query and table will be populated with the result of query.

    Reference:

    Save query results as a table

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, July 11, 2017 5:14 AM
    Moderator
  • Thanks everyone I finally got it to work with a 2nd table and a query!

    Regards Danny

    Tuesday, July 11, 2017 7:39 PM