replacement for IIF statement?

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

     

Answer 1

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)

Answer 2

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 [e-mail].

Answer 3

You could also write a function and use the case statement.

Answer 4

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?

Answer 5

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.

Answer 6

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)

Answer 7

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 [e-mail].

Answer 8

Thanks everyone I finally got it to work with a 2nd table and a query!

Regards Danny