if/case calc between 2 numbers
is it possible to calculate between 2 numbers using an if/case calculation?
for example: if x [is between] 1 and 2; "hello"; ""
or: case(x > 1 and < 2; "hello"; x > 2 and < 3; "goodbye";...
If ( FieldA >= 1 and FieldA <= 2 ; "Hello" ; "Goodbye" )
... or ...
FieldA < 2 ; "Hello" ;
FieldA < 3 ; "Goodbye" ;
Field A < 4 ; "Sometimes" ;
If(), Case(), AND, NOT and others will stop evaluating (short-circuit) when they hit the first true so in the case of the ranges, you put them in order so your records evaluate and stop in line where appropriate. Branch prediction is very valuable particularly when you have records where (out of 100,000) that 80,000 are of one value (for example). The right order of evaluation can make a difference in evaluation time since it can evaluate the majority of the records immediately. This is more true on complex calcs but the theory holds. So if (in the above) the majority of the records were 'over 3' then you would would reverse the calc.
In the above Case(), the "never" is the result if none of the rest are true and it is known as the default result. If the field should be blank then leave off a default (as indicated in blue). Same with the If() example.
UPDATE: This forum double-spaced my calc; I didn't. But I always list each of my tests on a new line because it makes it easier to understand and I wanted you to clearly see the logic.
Retrieving data ...