Range of numbers on a "Case" function

[DLM]

Guys, I have the following calculaiton:

Case ( Service = "Call" and Type = "International" and Number = "xxxxxxxxxx", 0.30 , etc...

On the xxxxxxxxxx I want to specify a number range, e.g., from 100 to 100000000.

When I do searches I simply enter 100...100000000 because "..." refers to range but in the calculation it won't work.

How should I do it?

[comment]

This is a price list, right?

[DLM]

This is a price list, right?

Yes it is, sort of.

[comment]

It's not good practice to hard-code prices into calculations, because eventually they will change. You should have a Prices table and lookup the price from there via a relationship.

What do the numbers represent? Are their ranges consecutive?

[DLM]

It's not good practice to hard-code prices into calculations, because eventually they will change. You should have a Prices table and lookup the price from there via a relationship.

What do the numbers represent? Are their ranges consecutive?

Hi comment -- I got it on the Prices tables, thank you very much for that suggestion, makes sense, however, for this particular DB is not really necessary. The numbers represent phone numbers (the "xxxxxxx" on my original post). Yes, they are consecutive.

[comment]

Even so, data belongs in fields, not in formulas.

If you want to use a calculation, you need to make it along the lines of:

Case (

Service = "Call" and Type = "International" and 100 < Number and Number ≤ 100000 ; 0.3 ;

...

)

Since Case() returns the result of the first test that is true, you can test for range boundaries only, for example:

Case (

n < 10 ; "zero to ten" ;

n < 100 ; "eleven to hundred" ;

...

)