Queennie L
asked on
IIF Statement in Access Query
This is what I had so far:
NewField: IIf([Field1]=True,[Field1] ,IIf([Fiel d2]=True,[ Field2],II f([Field3] =True,[Fie ld3],IIf([ Field4]=Tr ue,[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.
IIF-Multiple-Statements-in-Access-2007.d
NewField: IIf([Field1]=True,[Field1]
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.
IIF-Multiple-Statements-in-Access-2007.d
Try:
Newfield : Field1 AND Field2 AND Field3 AND Field4
Newfield : Field1 AND Field2 AND Field3 AND Field4
Actually, I think you mean:
NewField = [Field1] OR [Field2] OR [Field3] OR [Field4]
NewField = [Field1] OR [Field2] OR [Field3] OR [Field4]
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.
If each member of the team has to be good to select the team, then use AND.
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
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
Scott C
ASKER
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.
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.
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.
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.
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"
else
Newfield = "Overdue"
Exit Sub
End If
End If
End If
End If
If Field1 ="Complete" Then
If Field2 = "Complete" Then
If Field 3="Complete" Then
If Field 4= "Complete" Then
Newfield = "Complete/Whatever you want"
else
Newfield = "Overdue"
Exit Sub
End If
End If
End If
End If
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you.
NewField:IIf([Field1]+[Fie
Flyster