Results 1 to 6 of 6
  1. #1
    John_T is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2022
    Posts
    4

    IIF statement for Multiple Columns

    Hi,



    I have the following Query titled "State_Product".

    The query looks like the following:

    State Product1 Product2 Product3
    NY Yes Yes Yes
    IL NO Yes NO
    FL Maybe NO Yes
    GA NO NO NO
    KY Maybe Maybe Maybe

    I want to create another column - "Status". If columns - Product1, Product2 and Product3 all are matching then it is "OK" otherwise "Not OK". In example above, KY, NY and GA will be "OK" while IL and FL will show "Not OK". How do I do this?

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    instead of making lots of nexted IFs, make a function:
    in the query : Status: DoAll3Match([Fld1], [Fld2], [Fld3])


    Code:
    function DoAll3Match(pvFld1, pvFld2, pvFld3)
    on error resume next
    
    DoAll3Match= (pvfld1 = pvfld2) and (pvfld1 = pvFld3)
    end function

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Your design is not normalized, but something like this would work:

    IIf(Product1 = Product2 AND Product1 = Product3, "OK", "Not OK")
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    John_T is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2022
    Posts
    4
    Hi Ranman256. Thanks.

    If I need to add more fields, do I just change the Function to the following:

    Code:
    function DoAll3Match(pvFld1, pvFld2, pvFld3, pvFld4, pvFld5)
    on error resume next
    DoAll3Match= (pvfld1 = pvfld2) and (pvfld1 = pvFld3) and (pvfld1 = pvFld4) and (pvfld1 = pvFld5)
    end function

  5. #5
    John_T is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2022
    Posts
    4
    Thanks Paul.

  6. #6
    John_T is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2022
    Posts
    4
    Thank you to both of you. It worked really well.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 09-01-2019, 11:19 AM
  2. Replies: 2
    Last Post: 05-15-2014, 06:15 AM
  3. IIF statement to compare data in two columns
    By ecalvert in forum Access
    Replies: 2
    Last Post: 07-17-2012, 01:13 PM
  4. Replies: 9
    Last Post: 01-03-2012, 11:35 AM
  5. case statement - multiple columns
    By eddiec in forum Queries
    Replies: 2
    Last Post: 07-05-2011, 03:24 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums