Case Statement not working for me

[Baylah]

Good morning,

I have a field that returns a value but can not create a report from this field because there is an infinite number of possible returns and the "count of the records" is just of little value.

So what I would like to do is in a seperate field use a calculation to state that if a number falls between X and Y then Z.

My current case statements is as below. The problem is that it only evaluates the first expression. All records in the DB (if I use replace field contents) receives the value, 0 to -4 after running this calc.

Case

VAR<0>-4 ;"0 to -4";

VAR<-8>-12;" -8 to -12";

VAR<-12>-16;"-12 to - 16";

VAR<-16>-20;"-16 to -20";

VAR<-20>-24;"-20 to - 24";

VAR<-24>-48;"-24 to-48";

VAR>0<4; "0 to 4";

VAR>4<8; "4 to 8";

VAR>8<12; "8 to 12";

VAR>12<16; "12 to 16";

VAR>16<20; "16 to 20";

VAR>20<24; "20 to 24";

VAR>24<48; "24 to 48";"")

Can anyone help me out? I tried this with an if/then or but that didn;t work either.

if (var<0>-4;"0 to -4" or

if (var<-4>-8;"-4 to -8" etc, etc, etc.

Thanks,

Steve

If you choose not to decide you still have made a choice. Neil Peart

Edited January 17, 2007 by Guest

[Salesman0Gil]

Could you attach a sample file?

[comment]

VAR>0<4 is not a valid expression. It needs to be:

VAR > 0 and VAR < 4

Note that the way you are going, the boundary values (0, 4, 8, 12, etc.) will not return TRUE on any test. Consider instead:

VAR ≥ 0 and VAR < 4

If you arrange the tests in ascending order of the boundaries, you can make it shorter:

Case (

...

Var ≤ -4 ; ... ;

VAR ≤ 0 ; ... ;

VAR ≤ 4 ; ... ;

...

)

[LaRetta]

Hi Baylah,

The logic doesn't fit.

> 8 and < 12

> 12 and < 16

12 is skipped, as is each starting number through your series. If you use Comment's logic, you will probably catch it but I wanted to let you know. Also, we have a pretty pattern here that, if someone was inclined, could save many evaluations. It isn't as easy to understand but I wanted to provide it as another option if anyone is interested.

[color:green]Case ( VAR > 48 or VAR < - 48 ; "" ;

Let ( base = Floor ( VAR / 4 ) * 4 ; base & " to " & base + 3 )

[color:green])

This produces:

On 0: 0 to 3

On 1: 0 to 3

On 2: 0 to 3

On 3: 0 to 3

On 4: 4 to 7

On 5: 4 to 7 ... etc

Your result overlapped. If that's truly how you want it displayed then change the 3 in my calc to 4 which will then produce 0 to 4, 4 to 8 etc. By eliminating the outside parameters (which don't fit the pattern) first (green), we don't have to deal with them within the pattern.

LaRetta :smile2:

[comment]

Good point - I didn't notice the steps were constant. But a label like "0 to 3" doesn't seem fitting when the value is 3.9, for example. I think it's quite common to say "between 20 and 30" when you mean "at least 20, but less than thirty".

[LaRetta]

Labels which say:

0 to 4

4 to 8

Ah. The use of to instead of through. Makes sense. Change my 3 to a 4. :wink2:

Edited January 18, 2007 by Guest
Changed entire post.

[LaRetta]

Oh! And you can save one more evaluation if you use this instead:

Case ( VAR > 48 or VAR < - 48 ; "" ;

Let ( base = Div ( VAR ; 4 ) ; base & " to " & base + 4 )

)

Every evaluation counts. Thank you for the tip, Michael. :wink2:

Edited January 18, 2007 by Guest
corrected syntax