IIF Statement

[lmg0115]

Everyone please help me make this IIF statement work. I have the following query which is derived from a union query:

SELECT TestHDRPRUnionqryforProdRpt.Date, TestHDRPRUnionqryforProdRpt.Line, TestHDRPRUnionqryforProdRpt.Shift, IIf(IsNull([Customers]![Customer #]),[Workorders]![customer number],[Customers]![Customer Name]) AS [Customer Name], TestHDRPRUnionqryforProdRpt.[Work Order], Workorders.[Part Number] AS Assembly, IIf([TestHdrPRUnionqryforProdRpt].[MyField]="none",[TestHDRPRUnionqryforProdRpt].[Printer Operator ID]) AS PrinterOP, IIf([TestHdrPRUnionqryforProdRpt].[MyField]="A" Or "B",[TestHDRPRUnionqryforProdRpt].[Printer Operator ID]) AS HeaderOP
FROM (Customers RIGHT JOIN Workorders ON Customers.[Customer #] = Workorders.[customer number]) RIGHT JOIN TestHDRPRUnionqryforProdRpt ON Workorders.[WO#] = TestHDRPRUnionqryforProdRpt.[Work Order];

It works up until the IIF statement for HeaderOP. I would like for it to look at the MyField field and if there is an A or B, then pull the name listed in the Printer Operator ID field and if it says none then leave blank. It works in the previous IIF statement for PrinterOp but in the HeaderOp statement it is pulling all of the names irrespective of A, B or none. What am I doing wrong? Thanks in advance for your assistance.

 

[MSAccessRookie]

[FONT=Courier New][COLOR=black]SELECT TestHDRPRUnionqryforProdRpt.Date,[/COLOR][/FONT]
[FONT=Courier New][COLOR=black]    TestHDRPRUnionqryforProdRpt.Line,[/COLOR][/FONT]
[FONT=Courier New][COLOR=black]    TestHDRPRUnionqryforProdRpt.Shift,[/COLOR][/FONT]
[FONT=Courier New][COLOR=black]    IIf( IsNull([Customers]![Customer #]),[/COLOR][/FONT]
[FONT=Courier New][COLOR=black]         [Workorders]![customer number], [/COLOR][/FONT]
[FONT=Courier New][COLOR=black]         [Customers]![Customer Name]) AS [Customer Name],[/COLOR][/FONT]
[FONT=Courier New][COLOR=black]    TestHDRPRUnionqryforProdRpt.[Work Order],[/COLOR][/FONT]
[FONT=Courier New][COLOR=black]    Workorders.[Part Number] AS Assembly,[/COLOR][/FONT]
[FONT=Courier New][COLOR=black]    IIf( [TestHdrPRUnionqryforProdRpt].[MyField]="none",[/COLOR][/FONT]
[FONT=Courier New][COLOR=black]         [TestHDRPRUnionqryforProdRpt].[Printer Operator ID]) AS PrinterOP,[/COLOR][/FONT]
[FONT=Courier New][COLOR=black]         IIf( [COLOR=red][B][TestHdrPRUnionqryforProdRpt].[MyField]="A" Or "B"[/B][/COLOR],[/COLOR][/FONT]
[FONT=Courier New][COLOR=black]              [TestHDRPRUnionqryforProdRpt].[Printer Operator ID]) AS HeaderOP[/COLOR][/FONT]

I have reformatted your query to make it easier for me to read, and highlighted a potential problem area. I would also like to ask a question about the IIf Structure.

1. Your first and second IIf Statements have three parameters, and the other one only has two parameters. My understanding is that the standard IIf Statement has three parameters:
   1. Expression to test
   2. Condition/Value if TRUE
   3. Condition/Value if FALSE
2. The phrase [TestHdrPRUnionqryforProdRpt].[MyField]="A" Or "B" is not the correct syntax. I believe that [TestHdrPRUnionqryforProdRpt].[MyField] IN ("A", "B") will give you what you are looking for.
   

 

[lmg0115]

I am confused by your statement

and the other one only has two parameters

This statement is exactly like the one above it, the only difference is MyField = A,B instead of "none". Am I missing something? I'm still very new at this and want to make sure I understand where my errors are and how to correct them.

In addition, the corrected syntax returned no records. Here is what I changed it to:

HeaderOP: IIf([TestHdrPRUnionqryforProdRpt].[MyField] In ("A","B"),[TestHDRPRUnionqryforProdRpt].[Printer Operator ID])

Did I leave something out?

 

[MSAccessRookie]

I am confused by your statement
This statement is exactly like the one above it, the only difference is MyField = A,B instead of "none". Am I missing something? I'm still very new at this and want to make sure I understand where my errors are and how to correct them.

In addition, the corrected syntax returned no records. Here is what I changed it to:

HeaderOP: IIf([TestHdrPRUnionqryforProdRpt].[MyField] In ("A","B"),[TestHDRPRUnionqryforProdRpt].[Printer Operator ID])

Did I leave something out?

The IIf statement has

1. Expression to test
   • [TestHdrPRUnionqryforProdRpt].[MyField] In ("A","B")
2. Condition/Value if TRUE
   • [TestHDRPRUnionqryforProdRpt].[Printer Operator ID]
3. Condition/Value if FALSE
   • I do not see this parameter.

 

[lmg0115]

That's what I missed. Thank you.

 

[MSAccessRookie]

That's what I missed. Thank you.

Glad to be of help. Good luck in your project.

 

[lmg0115]

One more quick question. . . I would like the Printer Operator ID to print the Last and First name, how do I go about adding this statement to my IIF statement:

=[tbl_Employees]![Last_Name] & " " & [tbl_Employees]![First_Name]

I tried adding it in the criteria field but it doesn't do anything I still see the ID number instead of the name.