If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register or Login
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

find nearest value database access

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.

Re: find nearest value database access

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.

Re: find nearest value database access

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.

Re: find nearest value database access

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?

Code:

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

Re: find nearest value database access

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