help with iif statement in access

[ariel20029]

Hi all,
I know access is not the best at formulas, but I have certain customer names were they can have the name entered slightly differently ( like Microsoft, Microsoft Inc, Microsoft UK)..
Is there a iif contains formula so that if the customer name contains Microsoft then return the word Microsoft else ROW ( Rest of World)?

I would build a nested iif for the 10 or so customers that I need to identify if there is a formula that will work.




My only other option I can do is build a table and maintain that table for those select customers ( kind of a pain but a workaround).

thanks for your help with the iif statement.
Sharon

 

[stumac]

I would say adding a basic table with 2 columns is the best option for this, it is a lot easier to maintain than updating formula across all queries/reports/forms.

Nested IIF will have a limitation (it used to be 7), if you want to use the formula option I would recommend Switch:

CompanyName: Switch([MyField] Like "*Microsoft*","Microsoft",[MyField] Like "*Google*","Google",[COLOR=#b22222]True,[MyField][/COLOR])

I think you can have up to 15 conditions, but think this could also be nested to increase that. Note the last condition above is simply True, this is evaluated left to right, therefore if it gets to the last condition and hasn't met any criteria the last one will be met and it will return the value of Myfield.

 

[ariel20029]

Thank you so much this is great!
Have a great day.
Sharon