Help with clearing fields

[Rangoon]

Here is what I want to have happen. If 9/15/02 is entered into the PromoStartDate field and 9/26/02 is entered into the PromoStopDate field, on 9/27/02 I want both fields to be

[spb]

It looks like your Case calculations are set up to populate the field, but do not have another that will empty the fields. So they get populated but never emptied. You need an extra Case option in each statement that tests if StopDate is less than CurrentDate, and if so the action is "" (to clear the field.

Steve Brown

[Rangoon]

Steve,

Where in the calculation does the new case option go? Would you add it to the actual calculation...

Thanks,

HJ

[spb]

With a Case function, you need to place your criterion, then a comma, then the result, then a comma, then the next criterion, etc. It looks like your statements need three criteria each. I'll list them vertically, followed by desired result:

IsEmpty(PromoStopDate), PromoStartDate

PromoStopDate + 1 > Status( CurrentDate), PromoStartDate

PromoStopDate < Status( CurrentDate), ""

The first two you combined with an "or". But the third is missing altogether. This one will clear the field if the StopDate is earlier than the current date. Also, if you combine the first two with an "or", then it looks like you are setting up an If statement, not a Case, i.e., If (criteria A), then Result A, else Result B. I'd say try rewriting your Case as three situations like this (I'll just do your first one)

Case(IsEmpty(PromoStopDate), PromoStartDate, PromoStopDate + 1 > Status( CurrentDate), PromoStartDate, PromoStopDate < Status( CurrentDate), "")

In doing elaborate statements like this, it is conventional to insert hard returns and leading spaces to make then easier to read (within the calculation dialog box). FM will pay no attention to these. So we could make your Case look like this:

Case(IsEmpty(PromoStopDate), PromoStartDate,

PromoStopDate + 1 > Status( CurrentDate), PromoStartDate,

PromoStopDate < Status( CurrentDate), "")

Now it should be easier to see what is happening.

Steve Brown