help with iif statement in access

ariel20029

Board Regular
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

Active Member
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.
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Macro to copy values across rows and transposing them and add the user id
    [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Hi,[/COLOR][/SIZE][/FONT] [FONT=Times New...
Top