Is there a way to put IIF statement criteria into a table?

I have some data with a list of Product IDs and I want to assign the Product IDs a category name based on specified criteria. Some examples are below.

IIF Product ID = #*C#* then category name = Cable
IIF Product ID = 00#* or 497* then category name = Component
...

I can get it to work using multiple IIF statements:

Product Type: IIf([Product ID] Like "#*C#*","Cable",IIf([Product ID] Like "00#*" Or [Product ID] Like "497#*","Component",IIf([PIDType4]="7346M" Or [PIDType5]="CPP7346M","7346M","Regular Product")))

But I have a lot more criteria I would like to enter in and gets hard to manage it this way. Is there somehow I can create a table with all of these criteria and just reference the table instead?

Thanks,
Adam

Welcome to the forum.

As you say this is going to just get more messy. There's no real relationship to hang on to so what I would do is write a series of individual queries - one for each rule. Then use a macro to run all the queries to update the Product Type field. This will at least make the process easy to maintain e.g. add more rules.

hth
Chris

I've found how to search fields in a table, so without needing to make an if statement you can just create a list of what would fit into each category, but I don't know how to update a field based on which field it found the result in. In fact I have been trying to get help with the exact same question for a few days now.

What I would need to do to tag my records table with the category is figure out how to add an if statement to a select or update query that returns which field the word was matched from.

My thread:
http://www.access-programmers.co.uk/forums/showthread.php?t=194841

That may help you out some.

You don't need to update the field. You store the ID and when you have a query you link your table with your data on the ID and you can get the name. But you don't store the name again.

If you make a select query that returns the record/s you require, then just turn this query into an Update Query and update the field accordingly.

Found the answer to what you want, you can either have it be a select query, or turn it into an update query. Ironically you answered part of what I wanted, now I just need to figure out how to do some specifics.

This code references a table's fields as criteria for the category, allowing you to enter columns of criteria and labeling the criteria with whatever name you want based on the field name it finds the word in.

In my code it uses a record table called SAP-Dump and selects every record that matches a word from the lookup table's 3 categories. When it does find it, the IIF statement fills the last column with a category word of your choosing.
Edit--
Above is the sql from a different query than the one that is better at categorizing, let me get this all working and I will make a better post with a more complete answer if this doesn't help enough.

Here is the query that checks every entry in a column, but I currently only have it set up to check one column/field. I will be tweaking and when I get it doing all of them and exactly what I want I will re-post: