SQL Command for ranges

[mikkaro]

hello everyone.. i apologize if there have been other entries like this but i just had to this last minute thing and microsoft access and vb6 is killing me right now..

so i have a table with a "minimum", "maximum", and "amount" field all set to number data type, double, fixed, and 2 decimal places

in my program i have a textbox that contains a certain value.. once i click the button it is supposed to search the database for where the value on the textbox is in between the minimum and the maximum..

E.G.

minimum maximum amount
0.00 499.00 100.00
500.00 599.00 200.00
600.00 699.00 300.00

text1.text = 555.00

what is the most appropriate sql for this so i get the 200.00 amount needed
since 555.00 is in between 500.00 and 599.00

i am sooo looking forward to an answer.. thank you sooo much in advance to whoever helps out.

[Always_Confused]

Dunno

select Amount
From SomeTable
Where (text1 >= [minimum]) AND (text1 <= [maximum])

[si_the_geek]

Welcome to VBForums

Thread moved to 'Database Development' forum (the 'VB6' forum is only meant for questions which don't fit in more specific forums)

[wild_bill]

Reply in SQL server you could use a case statement. I am not familiar enough with access to know if this syntax will work, but you could give it a try:

declare @input money; set @input = 555.00

select
	case 
		when @input between 0.00 and 499.00 then 100.00
		when @input between 500.00 and 599.00 then 200.00
	    when @input between 600.00 and 699.00 then 300.00
	end

[techgnome]

pifft! No need for the select... the ranges are in a table, right?

SELECT * FROM tblRanges WHERE @Input BETWEEN MinField and MaxField

Of course replace @Input with what ever means to get your value into it.

-tg

[Always_Confused]

of course Technome is right. i created a table and tested it