IIF Function multiple date ranges

Question

Hello, little rusty and need some help.

Need to write an IFF function to include the following:

If (Production Date) is between 10/22/13 and 1/20/14, then W

If (Production Date is between 7/16/13 and 10/21/13 then X

If (Production Date) is before 7/16/13 then Y,

If (Productiuon Date is blank, then Z

How can I pull this all together?

Thank you

Answer 1

Hi, Jessica

IIF([Production Date] >= #1/21/14#,"New Hire",

IIF([Production Date] >= #10/22/13# and [Production Date]  <= #1/20/14#,"W",

IIF([Production Date] >= #7/16/13# and [Production Date]  <= #10/21/13#,"X",

IIF([Production Date] < #7/16/13# ,"Y",

IIF([Production Date] Is Null,"Z")))))

or also

IIF([Production Date] >= #10/22/13# and [Production Date]  <= #1/20/14#,"W",

IIF([Production Date] >= #7/16/13# and [Production Date]  <= #10/21/13#,"X",

IIF([Production Date] < #7/16/13# ,"Y",

IIF([Production Date] Is Null,"Z","New Hire"))))

The IIF structure is:  IIF(condition,what do if true, what do if false)

Bye Mimmo

Answer 2

Hi Jessica

IIF([Production Date] >= #10/22/13# and [Production Date]  <= #1/20/14#,"W",

IIF([Production Date] >= #7/16/13# and [Production Date]  <= #10/21/13#,"X",

IIF([Production Date] < #7/16/13# ,"Y",

IIF([Production Date] = "","Z"))))

Mimmo

Answer 3

Perfect... One last question..

I'm getting an #error on any production date greater than or equal to 1/21/14. I tried adding

IIF([Production Date] >=#1/21/14#,"New Hire" and added an addition ) at the end, but the #error is still showing up.

Answer 4

Try with:

IIF([Production Date] >= #10/22/13# and [Production Date]  <= #1/20/14#,"W",

IIF([Production Date] >= #7/16/13# and [Production Date]  <= #10/21/13#,"X",

IIF([Production Date] < #7/16/13# ,"Y",

IIF([Production Date] Is Null,"Z"))))

Mimmo

Answer 5

Thank You very helpful.  The only piece not working is if the Product Date field is blank (empty), it needs to put Z, however it's leaving it blank