IIF Statement with OR 1

[JS1521253]

I am trying to write a query that will tag a record if it meets certain criteria. I have a couple different criteria that I want to separate using OR, but I'm not sure how to do this within the select statement.

I'm am currently getting "0" or "-1" with the following statement:

(
IIf([dollar]>9999.99,">$10,000","")
OR (IIf([dollar]>4999.99,">$5,000","")
OR (IIf([dollar]>999.99,">$1,000","")
)

Ultimately, 1 record could have 3 results. I'd be grouping by this result, so I could have the same record show up 3 times.

Thanks

 

[r937]

could you explain this please --

Ultimately, 1 record could have 3 results. I'd be grouping by this result, so I could have the same record show up 3 times.

maybe you could show some sample rows of data and the results you expect to see from the query

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[PHV]

IIf([dollar]>9999.99,">$10,000",IIf([dollar]>4999.99,">$5,000",IIf([dollar]>999.99,">$1,000","")))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[r937]

PHV, that doesn't generate 3 records, does it

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[MajP]

A better way

tblRanges
rngText
minRange
maxRange

rangeText	minRange	maxRange
> 1,000 	$1,000.00	$4,999.99
> 5,000	        $5,000.00	$9,999.99
> 10,000	$10,000.00	$9,999,999,999,999.00

Single record

SELECT tblValues.dollar, tblRanges.rangeText, tblRanges.minRange, tblRanges.maxRange
FROM tblValues, tblRanges
WHERE (((tblValues.dollar)>=[minRange] And (tblValues.dollar)<=[Maxrange]))
ORDER BY tblValues.dollar;

dollar	        rangeText
$1,000.00	> 1,000
$4,999.99	> 1,000
$5,000.00	> 5,000
$9,999.99	> 5,000
$10,000.00	> 10,000
$1,000,000.00	> 10,000

or all ranges (I assume when you say 3 records)

SELECT tblValues.dollar, tblRanges.rangeText, tblRanges.minRange, tblRanges.maxRange
FROM tblValues, tblRanges
WHERE (((tblValues.dollar)>=[minRange]))
ORDER BY tblValues.dollar;

dollar	        rangeText
$1,000.00	> 1,000
$4,999.99	> 1,000
$5,000.00	> 1,000
$5,000.00	> 5,000
$9,999.99	> 1,000
$9,999.99	> 5,000
$10,000.00	> 1,000
$10,000.00	> 5,000
$10,000.00	> 10,000
$1,000,000.00	> 1,000
$1,000,000.00	> 5,000
$1,000,000.00	> 10,000

Way more flexible, way faster. Can and or change ranges at anytime.

 

[JS1521253]

Here is a sample of what I would be looking for:

AccountNumber Client Dollar Output
1005001 ClientA 25000 >10,000
1005001 ClientA 25000 >5,000
1005001 ClientA 25000 >1,000
1005020 ClientB 11000 >5,000
1005020 ClientB 11000 >5,000
1006010 ClientC 1200 >1,000

Thank you for your help on this...

 

[JS1521253]

Sorry, I didn't see your response MajP. I think a new table is probably in order. I'll try that and see what happens. Thanks.

 

[PHV]

SELECT AccountNumber, Client, Dollar, ">$10,000" AS Output
FROM yourTable WHERE Dollar>=10000
UNION SELECT AccountNumber, Client, Dollar, ">$ 5,000"
FROM yourTable WHERE Dollar>=5000
UNION SELECT AccountNumber, Client, Dollar, ">$ 1,000"
FROM yourTable WHERE Dollar>=1000
UNION SELECT AccountNumber, Client, Dollar, ""
FROM yourTable WHERE Dollar<1000

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886