Results 1 to 3 of 3

Thread: help with iif statement in access
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2013
    Posts
    86
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default help with iif statement in access

    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

  2. #2
    Board Regular
    Join Date
    Jul 2010
    Posts
    459
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: help with iif statement in access

    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",True,[MyField])
    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.

  3. #3
    Board Regular
    Join Date
    Jun 2013
    Posts
    86
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: help with iif statement in access

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •