query to get next highest value

[suraj14]

This the access data table
item value
GS40 300
GS63 600
GS80 1000
GS90 4000
GS95 5000
GS98 8000

I am using Vb6 and access 2010 database

In the above data if i select the value 600 then
I want a query in vb to get next 2 higher values than 600

Please help me to to get the query

[DataMiser]

Well assuming that the field is a numeric field you could use a simple query

Something like

select top 2 item,value where value >600 order by value

In this case it would return 1000 and 4000
If the field is text then it would return 8000 only since that is the only one where the first digit >6

Of course you would need to substitute a variable in there for the 600 so it can work with other values.
Also would be helpful to know if you are actually using VB6 or if you are talking about the VB that is included in Access which is not really VB at all but rather VBA

[suraj14]

Hi,

Thanks for your help.

Yes i am using VB6.

i have tried the code, the code is:-

gear_box_table.Open ("select top 2 MODEL_NO,OUTPUT_TORQUE from gear_box_table where MAX_FLANGE = " _
& dblequote & attachment & dblequote _
& " And OUTPUT_TORQUE <= " & Val(bto_min) & ") order by OUTPUT_TORQUE")

it is giving some error:

Run-time error '-2147217900 (80040e14)':

<<<one more query

suppose if i get the value 4028

then i would like to know the query to get nearest or closest value of 4028 in the value field

[DataMiser]

What is the error message?
If Max_Flange is a text field then the value needs to be surrounded by single quotes

[suraj14]

Yes now the code is working.

Thanks for the support.