Access-Calculated Field- Nested IIF

Question

I am a newbie, I need help understanding what I am doing incorrectly. I have a calculated field in the table and the expression I have entered is pasted below I am getting an invalid syntax:-

IIf([JPP-JPA_Received_Date]<=[JPP-JPA_Due_Date],"On Time", IIf([JPP-JPA_Received_Date]>[JPP-JPA_Due_Date],"Late", IIf([JPP-JPA_Received_Date],Is Null, AND IIF([JPP-JPA_Due_Date]<Date(),"Over Due"))))

Answer 1

A couple things are a little off.

The part where you are checking if [JPP-JPA_Received_Date] is null AND the [Jpp-JPA_Due Date] is prior to today, should all be contained in the condition section of a single Iif function.

You also may need to wrap each instance of [JPP-JPA_Received_Date] in an Nz() function. That's because if it is null, Access might get tripped up on expressions like Null <= Due Date.

Finally, I'd recommend the SWITCH function. It does something similar to nested if statements, but it's a little easier to follow.