using "Between" in a query calculated field

[David Ball]

Hi,

I am trying to use Between in a calculated field in a query and not having much luck. The formula I am trying is:

SizeRange:IIF([Size] Between 0 And 150, “0-150”),IIF([Size] Between 200 And 600, “200-600”, “600-1000”))

What do I need to change to get this working?

Thanks very much
Dave

 

[arnelgp]

SizeRange: Switch([Size]<151,"0=150",[Size]<601,"200-600",[Size]>600,"600-1000")

 

[Guus2005]

Hi David,

Between doesn't work in an immediate if statement.
You could rewrite it by checking for the bounderies
SizeRange:IIF([Size] >0 AND [Size] < 500, "0-150",IIF([Size] > 200 AND [Size] < 600,"200-600","600-1000"))

However you should know that an immediate if (IIF) in a query is very slow.
You could create a table Range with Minimum and Maximum values and the range in which they fall. You could join this table and display the SizeRange as a result.
Also there are two built-in functions Choose and Switch which you can use in cases like this.

HTH

 

[David Ball]

Thanks very much, guys. I will give it a go.

Dave