Finding the Closest match in access query

[AccessHelp123]

Hi,

I am trying to write a query which will update a Field table1.Cell_Id finding the closest match for table1.Text1 in table2.text1 and then updating table1.Cell_Id with table2.Cell_Id.

I tried using the LIKE statement but it updated about 3 values. I have hundreds of records to update. Can anyone please help. Thanks.

 

[PHV]

What is a closest match for you ? vague logic ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AccessHelp123]

PH,

The value in table1.Text1 and table2.Text2 is of the form 09:58:23.31 which is time. But it is in the database as a text value. so what I am really trying to find is closest value(time)in table1 to the value(time) in table2 and update the value table1.Cell_Id from table2.Cell_Id.

 

[MichaelRed]

As noted by PHV, you need some more specificity in what you mean by "close". It is simplistic to convert the string representation of the time to a numeric:

? cdbl(((9 * 60. * 60) + (58 * 60) + 23 + 0.31) / 8640)
 4.15547569444444 

? Format(cdbl(((9 * 60. * 60) + (58 * 60) + 23 + 0.31) / 86400), "hh:nn:ss")
09:58:23

as (briefly) illustrated

There are several procedures posted herein (Tek-Tops) which also illustrate the use of a ParamArray in finding the min (or max or other aggregate functions) of a set of values. I would presume to advise you to review some several of these with the perspective of modifying one to moreclosely suit your particular situation.





MichaelRed