iif statement using In

[Bryan F]

ValveResult: IIf(797 In (SELECT tblOptestTable.TestResultID FROM tblOpTestTable WHERE tblOpTestTable.WorkorderID = [WorkorderID]),"Failed","Passed")

This is a field in a query... i'm using grouping for the unique WorkorderID then i'm trying to get whether any one of the test results for this Workorder is a fail..."797"... if it is then it should say failed...well you can see what i'm doing...
anyway...they all come up failed... can i even do this?

 

[Jon K]

I think the problem lies in the criterion:-
WHERE tblOpTestTable.WorkorderID = [WorkorderID]

You need to tell Access where to find [WorkorderID].


Try this two queries (it would be easier to type/paste each SELECT statement in the SQL View of a new query):

qryOne:-
SELECT [tblOptestTable].[WorkorderID]
FROM tblOptestTable
GROUP BY [tblOptestTable].[WorkorderID];

qryTwo:-
SELECT [qryOne].[WorkorderID], IIf(797 In (SELECT tblOptestTable.TestResultID FROM tblOpTestTable WHERE tblOpTestTable.WorkorderID = qryOne.[WorkorderID]),"Failed","Passed") AS ValveResult
FROM qryOne;


Run the second query qryTwo.