Calculation involving a date field

[Rfox]

The price of an item varies based on the Date it was purchased.

Field DateEntered = the date the record was created (purchase date)

cSetPrice is designed to establish the price.

I have tried lots of things, but this would seem to be what should work:

Case ( Date Entered < 9/1/2002; 9.50; Date Entered > 9/1/2004; 11.50; 10.50)

My guess is that the date is the probelm, but a search for < or > works fine on the date field. But the formula is not working. I am always getting the default. Apparently the evaluation is not working.

The result should be a number. Thanks for the help.

[The Shadow]

You can't enter the literal dates like that, try this instead:

Case ( Date Entered < GetAsDate( "9/1/2002"); 9.50; Date Entered > GetAsDate("9/1/2004"); 11.50; 10.50)

Also, "Date Entered" should be a field set to be type date in Define Database.

[MoonShadow]

I would have used:

Case(DateEntered < Date(9;1;2002); 9.5; DateEntered > Date(8;31;2004); 11.5; 10.5)

Is there something I'm missing here? It seems a bit easier and I would have thought it would be more efficient ... I see no indication that it needs converted.

It should work whether DateEntered was text or date (as long as a correct date format) but it probably is date because her searching with < would produce incorrect search results on a text field (and create date was mentioned). I'm asking because I want to learn.

I changed the second result because it was not consistent. This year's price bracket should begin on 9/1 and not 9/2, if the logic follows.

It appears pricing will change yearly? Because time will slide by and you might forget to change it each year (in Define Fields). And someone will be making adjustments on 9/15/2005 for two week's worth of entries incorrectly priced. It might best be handled in Browse Mode so management can adjust the figures (on records) instead. It might be easier to maintain. Just some thoughts ...

[Ugo DI LUCA]

Hi,

Well, IMO if we want to consider System settings, then I'd say that Date( ) should be preferred to GetAsDate( ), as its structure (Month; Day; Year) is universally equivalent in all FileMaker versions.

But definitely DateEntered would be of date format, or you should use TextToDate( ) or its v7 equivalent in order for the calculation to really evaluates either it is ">" or "<".

I hope Shaun will explain the GetAsDate( ) choice, as I'm sure there must be a reason.

[Rfox]

Thanks so much for your help.

The Shadow got it right. It works great. I had tried a lot of things, but not that.

BTW, "DateEntered" field is a date format.

[The Shadow]

No, I didn't have a good reason for the GetAsDate(), just going with the simplest possible solution to the problem. I would agree using Date() is better in general, as it will work with all locales without having to change the date strings.