Help!! i need to get a closest match

tseng · February 2nd, 2005, 09:33 AM

I need to get he closest weight match from one table to another.
someone inputs a "weightrequired"in the "order" table.
Then in another table called "paint".
There is a "weight" column. The "weightrequired" must get the closest match to the "weight" and then put this into the "order" table.
I understand that it is quite easy but i can get my head around it.
It would be ebst to do it in VBA and use a loop to get the closest match but i haven't a clue how to do this as i am used to graphic design. This is the first time i have used access.
Thankyou for any help in advance.

pgtips · February 2nd, 2005, 11:19 AM

this will get the closest match
SELECT TOP 1 <FIELDS>
FROM <TABLE>
ORDER BY Abs(<Weight> - <WeightRequired>) ASC

tseng · February 2nd, 2005, 02:05 PM

So i put that as a module. in VBA.

tseng · February 4th, 2005, 07:44 PM

I have never used access before so i havent got a clue how to configure VB 6.0 codes for VBA.
as weight_given and turkey_weight are actually from a table. (see above posts)
And also this code needs to go down the table and try all the values from the table. The table will only have about 40 entries so it is quite easy to put in a simple code like this.
please help...

Code:

Function find_match()
Dim weight_given As Integer
Dim weighttemp As Integer
weight_given = Val(text1.Text)
Turkey_weight= Val(text2.text)
Do weight_given -Turkey_Weight
result = weightless
weighttemp = weightless \ 1   'to make sure all results are postive else it won't find the closest

weight_given -turkey_weight2
If result > weighttemp Then Loop
If result < weightemp Then weighttemp = result
Loop Until weighttemp = 0 'i will ensure there is a value of 0 at the bottom of tableweight

temp = actual_weight 'needs to be put into the invoice table somehow
End Function