My database tracks events. There are several different tasks I need to perform based on the current status of the event. I am trying to create a status field to mark records as one of five different categories depending on the records "state". I have been using the calculation below with some success. All of the "states" are working as expected except for the two "open" and "closed" states. I assume that the problem has something to do with how I am working with the dates but I'm kind of lost.

This is my current calculation. I've tried to move the order of the cases around and have tried different ways of ordering the "get current date" steps. Nothing seems to work. Any advice would be greatly appreciated.

Case (

Final Invoices checkbox = "Yes" ; "Final" ; //Black

Get ( CurrentDate ) ≥ Start Date and Get ( CurrentDate ) ≤ End Date ; TextColor ( "Open" ; RGB ( 24 ; 162 ; 75 ) ) ; //Dark Green

Get ( CurrentDate ) > End Date and Final Invoices checkbox ≠ "Yes" ; TextColor ( "Closed" ; RGB ( 255 ; 0 ;0 ) ) ; //Red

Exhibitor Kit Emailed checkbox = "Yes" and Get ( CurrentDate ) < Start Date; TextColor ( "Ordering" ; RGB ( 0 ; 0 ;255 ) ) ; //Blue

TextColor ( "Planning" ; RGB ( 0 ; 255 ;0 ) ) //Light Green

)

Are you sure your Start Date and End Date fields are actually defined as dates in Define Database?

I appreciate your response.

Yes they were set to date fields. I have solved my problem by simplifying things. I'm really not very confident that I understand what I did to fix this problem. I vaguely understand that it has to do with the order of the cases (?) in the script (?).

Case (

Final Invoices checkbox = "Yes" ; "Final" ;

Exhibitor Kit Emailed checkbox = "Yes" and Get (CurrentDate) < start date; "Ordering" ;

Get ( CurrentDate ) > end date ; "Closed" ;

Get ( CurrentDate ) ≥ start date ; "Open" ;

"Planning"

)

Thanks for letting us know.