Status based on dates

Question

I am trying to write a Status expression for In Process, Overdue, and Completed based on date differences Here is what I tried

=IIf([DueDate]<Date(),"Overdue",IIf([DueDate]>=Date(),"Inprocess",IIf([CompleteDate] Is Not Null,"Completed")))

The only one working correctly id the Complete data status

Answer 1

The expression says:

IF The DueDate is earlier than the current date, then sow Overdue

Else, if the DueDate is equal to or greater than the current date, then show In process.

Else, If the Completed Date isn't Null show Completed

However since the DueDate must be either earlier than the current date or greater/equal it will never get to the third condition.

I would try:

IIF(NOT IsNull(CompleteDate), "Completed", IIF(Duedate<Date(),"Overdue", "InProcess"))

This says if completed date is filled in show Completed,

Else If Duedate earlier than current Date show Overdue

Else show InProcess

Answer 2

Yes, I believe that is correct

Answer 3

Do you have the expression as the Control source?

Answer 4

I di have one question. The In-process status shows up in the field for a new record is there a way to keep that blank?

IIF(NOT IsNull(CompleteDate), "Completed", IIF(Duedate<Date(),"Overdue", "InProcess"))

Answer 5

That was awesome! Thank you for also explaining the logic and fallacy with the way I had written the expression. I truly appreciate the help!