SQL: Column for range value?

[milton747]

Hi folks,

I need to create a column to indicate Amount range: [ AmountRangeFlag.]

Starting SQL:
SELECT CustAlert.custID, CustAlert.Status, CustAlert.Amount
FROM CustAlert
ORDER BY CustAlert.Amount


Then:
If CustAlert.Amount 0-1000, then AmountRangeFlag = "A"
If CustAlert.Amount 1001-2000, then AmountRangeFlag = "B" etc...


Is this possible? (Need to drive a report with the SQL)

Thanks everyone.
Milton

 

[PHV]

A starting point:
SELECT custID, Status, Amount, Chr(65+Fix((Amount-1)/1000)) AS AmountRangeFlag
FROM CustAlert
ORDER BY Amount

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

 

[milton747]

Hi PHV,

Thanks for quick reply. I just tried the SQL.
I get an error:

22005: Invalid Procedure Call.

Doesn't if its the Fix of Chr? I have Access 2003 on XP2.
Any thoughts?

 

[PHV]

Depends of the values of Amount, I guess.
What about this ?
SELECT custID, Status, Amount, Chr(65+Fix((Amount-1)/1000)) AS AmountRangeFlag
FROM CustAlert
ORDER BY Amount
WHERE Amount Between 0 And 26000

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

 

[milton747]

Hi PHV,

I just got it going and you are absolutely right, Sir.

65+Fix((Amount-1)/1000000)) works good.

The amounts are bigger than I anticipated.

Very BIG Thank U.