How do I find the record that is closest to a given number?

[Seth Schrock]

I'm trying to simplify a very complex problem, so it is possible that I might leave out a few details, but I will try not to.

Basically, I have a table which has a number field (among others) that have a variable distance between the numbers. For example, the table might hold the following numbers:

98.891
100.664
101.527
102.344
103.135

I need to be able to type a number in a form and find the record whose number is closest to the one that I entered. For example if I enter 101.4, then it will find the third record. If I enter 100.7, it would find the second record. I have come up with a very complicated process of using two recordsets and looping through the table with the first recordset starting on the first record and the second recordset starting on the second and then do a .MoveNext on each at the same time and then testing if the value in recordset 1 is less than or equal to the value and recordset 2 is greater than or equal to the value so that I could find the two records that the value is between and then subtract the lower amount from the given amount and the given amount from the higher amount and compare the two results to see which one is closer. Again, this seems very complicated. I just hope someone can either come up with a better idea or just confirm that my idea is the only way to go.

Hopefully I made my question clear.

[Rabbit]

Try something like this:

SELECT *
FROM tableName
WHERE ABS(# - fieldName) = (
   SELECT MIN(ABS(# - fieldName))
   FROM tableName
)

Note that this can return multiple records if they are equidistant from the target.

[ADezii]

Assuming a Table Name of Table1, a Field Name of [Field1] containing the actual Values for comparison, and a Field named txtNumber on a Form that contains the Test Value, then the following Code will:

1. Create a Query consisting of the [Field1] Value along with the Minimal Variance between it and the actual Test Value.
2. Display the Query Results.
3. Code:
   

   On Error Resume Next
   Dim strSQL As String
   Dim qdf As DAO.QueryDef
   Const conQueryName As String = "Test Query"
    
   CurrentDb.QueryDefs.Delete conQueryName
    
   strSQL = "SELECT TOP 1 Table1.Field1, Abs(" & CSng(Me![txtNumber]) & "-[Field1]) AS Variance " & _
            "FROM Table1 ORDER BY Abs(" & CSng(Me![txtNumber]) & "-[Field1]);"
    
   Set qdf = CurrentDb.CreateQueryDef(conQueryName, strSQL)
    
   DoCmd.OpenQuery conQueryName

4. Results for Test Value of 100.7:
   

   Field1    Variance
   100.664    0.036
    

5. Results for Test Value of 101.4:
   

   Field1    Variance
   101.527    0.127
    

6. The SQL, along with the Results, should you wish to include the Test Value in the Text Box is:
   

   strSQL = "SELECT TOP 1 Table1.Field1, Abs(" & CSng(Me![txtNumber]) & "-[Field1]) AS Variance, " & _
             Me![txtNumber] & " FROM Table1 ORDER BY Abs(" & CSng(Me![txtNumber]) & "-[Field1]);"

   'Results for a Test Value of 101.4 in Me![txtNumber]
   Field1      Variance    Expr1002
   101.527     0.127       101.4

P.S. - Any questions, please feel free to ask.

[Seth Schrock]

Thank-you so much to both of you. I will try it out as soon as I can. I will be spending most of tomorrow tearing out old equipment, but hopefully I will get it done quickly so that I can try your solutions. I'm glad that there is a solution that is simpler than mine. I wasn't looking forward to putting my idea into code.

[NeoPa]

In technical terms, you simply find the absolute (signless) value after subtracting your value from the value in each record of the table. The record which reflects the smallest of these (in magnitude) is the one you're looking for.

[Seth Schrock]

Thanks Rabbit and ADezii for your solutions and to NeoPa for the explanation. I think that my final solution will be a combination of the two solutions, but that was so much simpler than my idea. I will choose Rabbit's as best answer as it is the simplest, but I will probably end up using a VBA implementation of the Rabbit's SQL so ADezii's code is certainly beneficial.

Thanks again.