help with iif statement in access

ariel20029

Board Regular
Joined
Jun 20, 2013
Messages
97
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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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:

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top