What's wrong with this statement for a query?
Waste Category: IIf([WasteName]![WasteNameID]=20 Or [WasteName]![WasteNameID]=26 Or [WasteName]![WasteNameID]=27 Or [WasteName]![WasteNameID]=28 Or [WasteName]![WasteNameID]=29 Or [WasteName]![WasteNameID]=30,"Oily Waste",IIf([WasteName]![WasteNameID]=8 Or [WasteName]![WasteNameID]=10 Or [WasteName]![WasteNameID]=11 Or [WasteName]![WasteNameID]=15,"Special Waste”, IIf([WasteName]![WasteNameID]=3 Or [WasteName]![WasteNameID]=17 Or [WasteName]![WasteNameID]=32,"NonHazardous Waste", “Hazardous Waste”)))
I keep getting error messages for invalid syntax.
Nested IIf statements won't work
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Nested IIf statements won't work
Did you create or edit the statement in Microsoft Word? Some of the quotes are "curly quotes" instead of "straight quotes", and Access can't handle curly quotes.
This should work:
Waste Category: IIf([WasteName]![WasteNameID]=20 Or [WasteName]![WasteNameID]=26 Or [WasteName]![WasteNameID]=27 Or [WasteName]![WasteNameID]=28 Or [WasteName]![WasteNameID]=29 Or [WasteName]![WasteNameID]=30,"Oily Waste",IIf([WasteName]![WasteNameID]=8 Or [WasteName]![WasteNameID]=10 Or [WasteName]![WasteNameID]=11 Or [WasteName]![WasteNameID]=15,"Special Waste", IIf([WasteName]![WasteNameID]=3 Or [WasteName]![WasteNameID]=17 Or [WasteName]![WasteNameID]=32,"NonHazardous Waste", "Hazardous Waste")))
This should work:
Waste Category: IIf([WasteName]![WasteNameID]=20 Or [WasteName]![WasteNameID]=26 Or [WasteName]![WasteNameID]=27 Or [WasteName]![WasteNameID]=28 Or [WasteName]![WasteNameID]=29 Or [WasteName]![WasteNameID]=30,"Oily Waste",IIf([WasteName]![WasteNameID]=8 Or [WasteName]![WasteNameID]=10 Or [WasteName]![WasteNameID]=11 Or [WasteName]![WasteNameID]=15,"Special Waste", IIf([WasteName]![WasteNameID]=3 Or [WasteName]![WasteNameID]=17 Or [WasteName]![WasteNameID]=32,"NonHazardous Waste", "Hazardous Waste")))
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Nested IIf statements won't work
A slightly shorter variant:
Waste Category: IIf([WasteName]![WasteNameID] In (20,26,27,28,29,30),"Oily Waste",IIf([WasteName]![WasteNameID] In (8,10,11,15),"Special Waste",IIf([WasteName]![WasteNameID] In (3,17,32),"NonHazardous Waste","Hazardous Waste")))
Waste Category: IIf([WasteName]![WasteNameID] In (20,26,27,28,29,30),"Oily Waste",IIf([WasteName]![WasteNameID] In (8,10,11,15),"Special Waste",IIf([WasteName]![WasteNameID] In (3,17,32),"NonHazardous Waste","Hazardous Waste")))
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 47
- Joined: 24 Oct 2011, 19:24
Re: Nested IIf statements won't work
OMG!!! I have been tearing my hair out over CURLY quotes??!?!
Your carefully prepared correction indeed ran the query.
Thank you.
Your carefully prepared correction indeed ran the query.
Thank you.