ok.. i have a database that keeps track of current projects that our art department is working on. in the database are 3 fields, pjStatus, pjDueDate, and pjCompleteDate. ok..

i want the status field to be a drop down list, that has options like; pending, in progress, overdue, and complete. probably a few more, and also let them type in there own if they need to... thats easy enough..

now the tricky part. how can i make that field automatically go to overdue if the duedate is passed, i was able to do this with a calculation field quite easy, but this does not let them modify the field if they need to.

also i have a script that when a button is hit it enters todays date into the pjCompleteDate field, i would like this same script to change pjStatus to Complete, which it wont let me do if it is a calculation field.

so basically i want a feild that if the project is overdue the field will say overdue, if it is complete, then it will say complete, but if it is neither overdue nor complete, i want the user to be able to put what ever they deem appropriate in the field.

did i confuse you yet?? hope not.. please help...

[ April 18, 2001: Message edited by: bman ]

You can do it using 2 fields. Make pjStatus a text field and allow entry. Create another field, say pjStatusCalc and make it a calculation: If(pjDueDate > Status(CurrentDate), "overdue", pjStatus).

Now for layout formatting. Place the pjStatusCalc field over the pjStatus field and don't allow entry into it. Make the pjStatus field transparent and give the pjStatusCal a background color.

thank man.. i will give it a try.. sounds like it should work to me..

ok.. what you gave me kinda worked.. i made a few modifications, and it works wonderfully now..

the only thing i change in your method was the calculation field, i made it look like this:

Case(PJDueDate < Status(CurrentDate) and IsEmpty(PJCompleteDate), "Overdue", PJStatus)

[ April 18, 2001: Message edited by: bman ]