combine date and text field?

[stevec]

Hi, I'm trying to make a field display something that could be either text or a SORTABLE date (otherwise I'd just make it a text field with no problem).

Here are my 3 fields:

AtPrePressDate (date field)

Completed (text field, which is a checkbox giving "x" when checked)

Status (a calculated date field) if(Completed="x", "Completed", AtPrepressDate)

AtPrepressDate obviously displays a date ok, otherwise I get the "?" in the field. I played around using the Case function instead, but couldn't really get it to work sufficiently either. Any ideas? I'm probably forgetting something simple, I always do that.

[BobWeaver]

The calculation result must be set to text, and then the formula should be:

Case(IsEmpty(Status),AtPressDate,Status)

Or, you may have to use:

Case(IsEmpty(Status),DateToText(AtPressDate),Status)

As for it being sortable, that's a problem unless your date can be formatted as YYYY-MM-DD.

[comment]

Try:

Case (

Completed = "x", "Completed",

Right ("0000000" & AtPrepressDate , 7 )

)

Set calculation result to text.