This works:

Case

(TYPE="AEWR" ,PRICE LIST 2003 DB::AEWR,

TYPE = "DEWR",PRICE LIST 2003 DB::DEWR,

TYPE = "DJFK",PRICE LIST 2003 DB::DJFK,

TYPE = "AJFK",PRICE LIST 2003 DB::AJFK,

TYPE = "DLAG",PRICE LIST 2003 DB::DLAG,

TYPE = "ALAG",PRICE LIST 2003 DB::ALAG)

but

this doesn't work

Case

(DATE < 11/14/2003 + TYPE="AEWR",PRICE LIST 2003 DB::AEWR,

DATE < 11/14/2003 + TYPE = "DEWR",PRICE LIST 2003 DB::DEWR,

DATE < 11/14/2003 + TYPE = "DJFK",PRICE LIST 2003 DB::DJFK,

DATE < 11/14/2003 + TYPE = "AJFK",PRICE LIST 2003 DB::AJFK,

DATE < 11/14/2003 + TYPE = "DLAG",PRICE LIST 2003 DB::DLAG,

DATE < 11/14/2003 + TYPE = "ALAG",PRICE LIST 2003 DB::ALAG)

and

this doesn't work

Case

(TYPE="AEWR" AND DATE <= 11/14/2003 ,PRICE LIST 2003 DB::AEWR,

TYPE = "DEWR" AND DATE <= 11/14/2003,PRICE LIST 2003 DB::DEWR,

TYPE = "DJFK" AND DATE <= 11/14/2003,PRICE LIST 2003 DB::DJFK,

TYPE = "AJFK" AND DATE <= 11/14/2003,PRICE LIST 2003 DB::AJFK,

TYPE = "DLAG" AND DATE <= 11/14/2003,PRICE LIST 2003 DB::DLAG,

TYPE = "ALAG" AND DATE <= 11/14/2003,PRICE LIST 2003 DB::ALAG)

the define fields accepts all of these calculations but the ones that do not work return no value whatsoever.

What an I missing?

I want to change my price list from 11/14/2003 while keeping the old price list the same as before that date for billing purposes.

Thank you,

Mitchell

Try Date( 11, 14, 2003 ) instead of 11/14/2003. '+' definitely won't work for logic testing. And in your third section, you have no result if TYPE is any of the possibilities listed and DATE > Date( 11, 14, 2003 ), thus no value returned.

Try something more like:

Case(

TYPE = "AEWR", Case( DATE < Date( 11, 15, 2003 ), PRICE LIST 2003 DB::AEWR, PriceToUseOtherwise ), TYPE = "DEWR", Case( DATE < Date( 11, 15, 2003 ), ...

)

NOTE: I use Date( 11, 15, 2003 ) to avoid using 'less than or equal to' syntax. I think it has more clarity that way. It's just a personal preference.

You don't really need to nest the case statements that way. This is shorter:

Case(DATE < Date(11,15,2003),

The reason it "doesn't" work is probably because 11/14/2003...in other words 11 divided by 14 divided by 2003 (answer: 0.0003922!)

(PS It must be really irritating when Bob tidies your code! No offence Bob - have to admit it does look much nicer)

I like to be irritating. It's my goal in life.

But, knowing how Queue is a stickler for efficient code, I couldn't help myself.

While tidier, I don't think it encompasses all the necessary information. You still need what the result would be for each TYPE, if the Date were after 11/14/2003, which I added since Mitchell appeared to omit it originally. You have it defaulting to "", which I don't think is what he's needing. Of course this is all based on interpretation of omitted data, but if I'm correct, then you'll need to nest Case()s either way.

I guess so, but the date condition is DATE<Date(11,15,2003) in every case. If the date test changes , then a single case statement could be used:

Case(TYPE="AEWR" and DATE < Date(11,15,2003),PRICE LIST 2003 DB::AEWR,

Exactly, that's his problem. He doesn't want it to return "". So we need something like:

Case(DATE < Date(11,15,2003),

Case(TYPE = "AEWR" ,PRICE LIST 2003 DB::AEWR,

TYPE = "DEWR" ,PRICE LIST 2003 DB::DEWR,

TYPE = "DJFK" ,PRICE LIST 2003 DB::DJFK,

TYPE = "AJFK" ,PRICE LIST 2003 DB::AJFK,

TYPE = "DLAG" ,PRICE LIST 2003 DB::DLAG,

TYPE = "ALAG" ,PRICE LIST 2003 DB::ALAG),

Case(TYPE = "AEWR", aewrprice,

TYPE = "DEWR", dewrprice,

TYPE = "DJFK", djfkprice,

TYPE = "AJFK", ajfkprice,

TYPE = "DLAG", dlagprice,

TYPE = "ALAG", alagprice)

)

Umm, okay. This must be the continuation of some other thread somewhere, because I didn't see that requirement here.

MITCHELL said:

The define fields accepts all of these calculations but the ones that do not work return no value whatsoever.

What an I missing?

I want to change my price list from 11/14/2003 while keeping the old price list the same as before that date for billing purposes.

I believe that would be it right there.

You mean I'm not allowed to skip over important details?

Not if we're debating their existence at the time.

MITCHELL said:

I want to change my price list from 11/14/2003 while keeping the old price list the same as before that date for billing purposes.

And now that the calc is solved, is it really the way to lock price lookup ? Because I have the feeling that's what's inside here.

It works fine now and I wish to thank all those who took the trouble to help me figure this out.

Mitchell