IIF Statement in Access Query

Posted on 2014-07-14
Last Modified: 2014-08-29
This is what I had so far:
NewField: IIf([Field1]=True,[Field1],IIf([Field2]=True,[Field2],IIf([Field3]=True,[Field3],IIf([Field4]=True,[Field4])))))

The above query is not giving me the right answer.

I have a Access query with 5 fields and IIF statements based on the 4 fields(Field1, Field2, Field3, Field4) into Newfield.

Please see attach example in word document.

Thank you.
Question by:Queennie L
    LVL 22

    Expert Comment

    So if any of the 4 fields are True, you want the 5th field to be True too? In that case you can use:


    LVL 30

    Expert Comment

    Newfield : Field1 AND Field2 AND Field3 AND Field4
    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    Actually, I think you mean:

    NewField = [Field1] OR [Field2] OR [Field3] OR [Field4]
    LVL 30

    Expert Comment

    If any member of the team has to be good to select the team, then use OR.
    If each member of the team has to be good to select the team, then use AND.
    LVL 20

    Expert Comment

    That's why I love building coded function for this stuff.  Nested IIF's can be confusing/hard to read.

    Function GetAnswer(byref field1 as Boolean, byref field2 as Boolean, byref field3 as Boolean, byref field4 as Boolean) as Boolean

    if field1 = true then
         GetAnswer = field1
    elseif field2 = true then
         GetAnswer = field2
    elseif field3 = true then
         Getanswer = field3
    elseif field4 = true then
         Getanswer = field4
    end If
    exit function

    Now, use the function in your query:
    YourAnswer= GetAnswer([field1],[field2],[field3],[field4])

    Scott C

    Author Comment

    by:Queennie L
    None of these give me what I needed.

    If Field1, Field2, Field3, Field4 has "complete" then show it in Newfield else show Overdue, Assigned and if null then leave it blank.

    I used the OR and AND.

    Thank you.
    LVL 30

    Expert Comment

    As you see Overdue is just mentioned in in the last comment.
    What does "has complete" mean?
    An example with meaningful data would speed up the solution.
    List variations of values and the required value of the IIF statement.
    LVL 3

    Expert Comment

    by:Leo Alexander
    If Field1, Field2, Field3, Field4 has "complete" then show it in Newfield else show Overdue, Assigned and if null then leave it blank.

    If Field1 ="Complete" Then
      If Field2 = "Complete" Then
        If Field 3="Complete" Then
          If Field 4= "Complete" Then
             Newfield = "Complete/Whatever you want"

              Newfield = "Overdue"
              Exit Sub
    End If
    End If
    End If
    End If
    LVL 1

    Accepted Solution

    Hi there,

    If you are using an access  query and not VBA (in order to create Case of If statements) maybe you should look into using the switch function:

    ex: Switch (SupplierID=1, "IBM", SupplierID=2, "HP", SupplierID=3, "Nvidia")

    Author Closing Comment

    by:Queennie L
    Thank you.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    This collection of functions covers all the normal rounding methods of just about any numeric value.
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now