I have following fields in my database : [Price], [Price1], [Price2], [Price3], [Price4] and [Qty].

Here is my problem:

If [Qty] is between 1 and 19, [Price] should display value from [Price1].

If [Qty] is between 20 and 40, [Price] should display value from [Price2]...etc.

Sounds easy, but my mind is empty after too many attempts.

Please help.

Hi jukuri,

Try this and see if it does what you want.

Case(

Qty <= 19, Price1,

Qty >= 20 and Qty <= 40, Price2,

Qty >= 21 and Qty <= 50, Price3,

Qty >= 51 , Price4,

"")

HTH

Lee

ALL RIGHT ,

It really did help.

I changed "," to ";" , like this:

Case(

Qty <= 19; Price1;

Qty >= 20 and Qty <= 40; Price2;

Qty >= 21 and Qty <= 50; Price3;

Qty >= 51 ; Price4;

"")

Now it works just fine!

Thank You

regards

Juha

Hi Juha,

I didn't know you were using a Non-US version. Why not update your profile to reflect you location and some of the other blank info. It can help us be more accurate with our answers.

Lee

p.s. Glad you got it working.

You can also use

Case( not IsEmpty(Qty);

Case(

Qty < 20; Price1;

Qty < 41; Price2;

Qty < 51; Price3;

Price4;

))

Note: I think Lee meant to use Qty >= 41, not 21.

Hi JT,

I believe that you didn't mean to have the last ; in your calc.

Good call! Now we're even.