Getting value in a text box based on range from table (1 Viewer)

rkrause

Registered User.
Local time
Today, 09:25
Joined
Sep 7, 2007
Messages
343
Heres my scenario:
i have a textbox with a point value for example is 225:
I have a table that looks like the table below. What i want to have is to beable to look at the textbox that has 225 value in it and look at the range in the table below and have the 1st column values 1-10 to show up in a textbox called txtGrade which would be a value of 2 for my example of using 225, im struggling on how to do this.
The below table is my data i have in my sql table. 3 fields called JobGrade LowPoint HighPoint

1 110 170
2 171 230
3 231 290
4 291 350
5 351 410
6 411 480
7 481 540
8 541 600
9 601 660
10 661 720

this is my sql statement i have now. in SSMS i can run this and i get the result i need but how can i get that into my vb studio and into the textbox i want it in

"select jobgrade from mgrdb.dbo.tblGradePointValues" & _
"where " & txtFinal.Text & " between LowPoint and HighPoint"

My issue is im not getting a number 1-10 based on the range to show in my textbox txtJGrade.txt.

so if my txtFinal.text is 225 i should get a 2 in the txtJgrade.txt and for another example if my txtfinal.txt was 530 i should get a 7 in the txtJgrade.txt

Any Ideas how to accomplish what im trying to do?
 

MarkK

bit cruncher
Local time
Today, 09:25
Joined
Mar 17, 2004
Messages
8,179
Your table would be more efficient like this...
Code:
#  Value  
0  110
1  170
2  230
3  290
4  ...
The second column is useless since it is always directly related to the first coumn of the next row.

Then do something like ...
Code:
SELECT TOP 1 [#] 
FROM tbl 
WHERE [Value] < YourTestValue
ORDER BY [Value] Desc
The idea being that you only select the items less than the one you are testing for, sort them, and then take the first one.
Does that make sense?
 

rkrause

Registered User.
Local time
Today, 09:25
Joined
Sep 7, 2007
Messages
343
Those 2 columns are directly tied to column 1.

i can retrive the value just fine, but im struggling on how to put it all together and get the value i want either 1-10 from my query into a txtbox in my vb code
 

MarkK

bit cruncher
Local time
Today, 09:25
Joined
Mar 17, 2004
Messages
8,179
With SQL we can filter and we can sort. So filter out all low points greater than your test value, and sort what's left descending. The first record in that list contains the jobgrade you want...
Code:
dim rst as dao.recordset
set rst = currentdb.openrecordset( _
  "SELECT jobgrade " & _
  "FROM tblGradePoints " & _
  "WHERE lowpoint < 225 " & _
  "ORDER BY lowpoint DESC;")
[COLOR="Green"]'the first record in this recordset contains the jobgrade you want[/COLOR]
msgbox rst.fields(0)
Do you see it now?
 

Users who are viewing this thread

Top Bottom