Between function in query Expr

[Worm]

Have a table with supplier name and sales to date. I would like to run a query that has a third col using an expr that puts either A B or c depending on the value

Expr1: IIf([SumOfNet value]>100000,"A",IIf([SumOfNet value]=0,"B",IIf([SumOfNet value]<100000,"C","")))


However I want to change the values to having a between range for each category

A between 0 and 50000
B between 50000 and 100000
C between 100000 and 1000000

But cant get the formula right.

[ranman256]

select 'A' as myFld from tbl where [SumOfNet value] between 0 and 50000
union
select 'B' as myFld from tbl where [SumOfNet value] between 50000 and 100000
union
select 'C' as myFld from tbl where [SumOfNet value] between 100000 and 1000000

[Worm]

Thanks for the quick reply.. however im not to sure where I would put that formula.....it took me ages to get my expr to work..

[ranman256]

its not a formula, its the query.

[Worm]

Hi

This is the query at the mo

SELECT Query1_1.[Supplier No], Query1_1.[Supplier Name], Query1_1.[SumOfNet value], Query1_1.Category, IIf([SumOfNet value]>100000,"A",IIf([SumOfNet value]=0,"B",IIf([SumOfNet value]<100000,"C",""))) AS Expr1
FROM Query1 AS Query1_1


Which results in below. I cant seem to integrate your code into to replace mine..... Sorry for the lack of skill

Supplier No	Supplier Name	SumOfNet value	Category	Expr1
11048	BLACKWELL UK LTD	50,592.35		C
11064	CAPITA BUSINESS SERVICES LTD	182,844.60		A

[June7]

BETWEEN AND operator is not necessary for your requirement. Just makes for a very long expression.

Choose() and Switch() can often be used instead of IIf().

Switch([SumOfNet]<=50000, "A", [SumOfNet]<=100000, "B", [SumOfNet]<=1000000, "C")

[Worm]

Thanks both for the replies. Ive never come across the Switch function before and it worked great... saved me much headache and good for future