find nearest value database access

[Jamil Nur M]

hello all..
great community with great people..

I have problems with the communication between VB.net and database access 2003.

How do I find closest/nearest value or on the form 1 (value.text) with data on form 2 (value of datagridview (access)).
Matching results are stored in the form of 1 (result.text) .. stored in the form 1 (result.text) ..
I attachment a picture of sample form but without program code..

I am sorry, If I am seeking help in simple thing or not able to explain it enough.

thank you very much.

[DataMiser]

You would have to loop through your datagrid and check each value in the given column and compare to the target value until you either find an exact match or tested them all. If the grid is sorted on that column then you could avoid testing them all with the proper logic.

I would suggest creating a public function on form2 that takes an input parameter and uses that to search the grid then returns an array to pass the data back to the other form.

[Jamil Nur M]

Thanks @DataMiser
Always use tags when posting code.
the code is not visible in this reply

[DataMiser]

There was no code in my reply... that part about code tags is in my signature just to remind people to use the code tags when they post code.

[Jamil Nur M]

I'm sorry...

this is my simple code, this is not in accordance with what is desired because the code is to look exactly the same value, not the closest value

code:

    Private Sub TextBox4_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox4.TextChanged
        conn.Open()
        cmd = New OleDbCommand("select*from data1 where value='" & TextBox4.Text & "'", conn)
        rd = cmd.ExecuteReader
        rd.Read()
        If rd.HasRows Then
            TextBox3.Text = rd.Item("data")
        End If
        conn.Close()
    End Sub

how about this code, I get this code from another article but I do not know to apply. Where I have to put it :

Select Top 1 * From data1 Order By ABS(Value-@SearchValue), Value

I have put in a few places but it always comes up a warning message
or maybe you have another solution,please help
Thanks

[DataMiser]

You need to have a temp variable to store your result and another to hold what you are searching for.
You then would loop through your grid checking each item against your search value and your temp var to see if the current item is closer to your search value than what is in temp var. If it is closer then replace the value in temp var with the current value from the grid and continue moving through the grid until you get to the end.

If the grid is sorted on that column then you could stop sooner. Once you get close then if you see the next value is farther away that the one you have then you would know that you have the one that would be closest. If it is not sorted then there is no way to tell until you test them all.

The exception would be if one were an exact match you would accept that one and exit the loop since it can't possibly be any closer.

[Jamil Nur M]

thank you very much for your attention and solutions...
forgive me if I was too hard to understand
I am new in the database and vb

is it possible the code looks like this?

Dim mileAge = Int32.Parse(Miles.Text) 
Dim rate = 0
For Each row In _test_2DataSet.test
    If mileAge <= row.MILESTO Then
        rate = row.TRUCKLOADRATE
        Exit For
    End If
Next
If rate <> 0 Then
    TxtRate.Text = rate.ToString
End If

[DataMiser]

It would appear that the code you posted is not checking to see if the value is close just if it is < or = and then when it is it quits looking. You need to make it a bit more complex than that checking each value and comparing the difference between that value and your search value and then comparing that difference to the closest you have found so far then only when you have found one that is = to the search value or checked them all can you know that you have the one that is closest