ok... i have a calculation field with this definition.

Case(

not IsEmpty(PJCompleteDate), "Complete",

PJStatus = "Stand By", "Stand By",

PJRequiredDate < Status(CurrentDate) and IsEmpty( PJCompleteDate ), "Overdue",

"Pending")

now this has always worked perfectly, displaying the correct info. until today.. today all work order that should come up as "Overdue" just come up as "Pending".

Now if you do something like change the PJRequiredDate, PJStatus, or the PJCompleteDate, then the field will update back to the correct thing. So I can fix it, but I'm just wondering why it would suddenly just stop working like that.

Thanks for the help,

Rick

Is the field stored or unstored? Is there a space (or some other character) in PJCompleteDate? Is PJRequiredDate a date field, and is the data in it regular?

Jerry

the storage options are "indexed" and the "do not store calculation results" is unchecked. PJRequiredDate is a date field, and no spaces in the PJCompleteDate field.. and all the data is regular dates... this suddenly just started happening on all the records that are supposed to show "overdue". it has been working fine up until today.

The storage options are "indexed" and the "do not store calculation results" is unchecked.

There you go, flip those settings. You want the field unstored or else Status(CurrentDate) will not update each day.

I'll do that...

any idea why it might have been working up until now? the database has been up and running for close to a year now?

Have you recently added or modified a relationship that uses this field as the right side key field?

the last change i made to the database that had anything to do with this field was creating another calc field with this calculation:

Case(PJStatusCalc = "Pending", "Pending", "")

but that was about a month ago, and the problem just started yesterday.

I would be tempted to say that someone did a find on the field and forced it to index. But that wouldn't make sense because a Status calc must be unstored, not just non-indexed. Does anyone else have access to Field Definitions?

the only other person with access is my boss, and he wouldn't have tried to make any changes.

as soon as I get a chance to shut down the database I'll make the changes to the field that were suggested, and that should fix the problem. It just bugs me that it worked for all that time, and then suddenly stopped working.