In A2010, I'm trying to auto fill a table field, Status, based on a somewhat complex conditional (think when library books are due, though that's not my situation):
IF DateRcvd is null AND DueDate < today THEN [book checked out and due but was never returned]
Status = “overdue”
ELSEIF DateRcvd is null AND DueDate > today THEN [book is checked out but not due yet]
Status = “not due”
ELSEIF DateRcvd > DueDate THEN [book returned late]
Status = “overdue”
ELSEIF DateRcvd < DueDate then [book returned on time]
Status = “on time”
ELSE
Status = “ “
END IF
This last ELSE Status="" may not be needed, but I'm explicitly trying to cover all situations.
DateRcvd is a Date field; DueDate is itself calculated based on the text field, PermitYear ("12/31/" & [PermitYear]), all in the table Permits.
Optimally, I would like the Status field to auto update in a DS form when the DateRcvd is updated. My brute force solutions have only been able to implement a portion of this, so I would truly appreciate help!
IF DateRcvd is null AND DueDate < today THEN [book checked out and due but was never returned]
Status = “overdue”
ELSEIF DateRcvd is null AND DueDate > today THEN [book is checked out but not due yet]
Status = “not due”
ELSEIF DateRcvd > DueDate THEN [book returned late]
Status = “overdue”
ELSEIF DateRcvd < DueDate then [book returned on time]
Status = “on time”
ELSE
Status = “ “
END IF
This last ELSE Status="" may not be needed, but I'm explicitly trying to cover all situations.
DateRcvd is a Date field; DueDate is itself calculated based on the text field, PermitYear ("12/31/" & [PermitYear]), all in the table Permits.
Optimally, I would like the Status field to auto update in a DS form when the DateRcvd is updated. My brute force solutions have only been able to implement a portion of this, so I would truly appreciate help!