Nearest results Issue

[hudaz]

Morning all.

I'm trying to create a query that will return the closest matches to what is input. However the closest i have gotten to this working correctly is by using the following criteria.

Like Left([Forms]![TESTTESTTEST]![Text2],1) & "*"

Now obviously this isn't a great solution as it just produces results that match the first number but i need it to display results closest to the number entered into text2.

Basically i am doing this as a designer can search for a gap on a machine where there new project can fit on causing the least amount of disruption. So by entering in the size of there project the query will display jobs of a similar size that are already planned on the machine.

does anyone have a solution to this please ?

Thanks in advance

 

[plog]

Generally to find the closest numeric value to a given one in a query you use the absolute value of a subtraction operation on the field you are searching. This would be done in a sub query, then based on that query you create another to find the Minimum difference, then you take that query and link it back to your main table to get the actual row.

Suppose you have this data

TestTable
fieldID, fieldVal, fieldName
2, 17, Jim
4, 4, Steve
7, 18, Terry
19, 8, Sally

This would be the SQL to find the value of each record from 13 (call this query 'sub1'):

SELECT fieldID,fieldVal, fieldName, Abs(13- fieldVal) AS ValDifference FROM TestTAble;

This is the SQL to find the lowest of the differences (call this query sub2):

SELECT Min(ValDifferences) AS LowestDifference FROM sub1;

This query links back to the first query (sub1) to get all the values of the record with the lowest differnce:

SELECT * FROM sub1 INNER JOIN sub2 ON sub2.LowestDifference=sub1.ValDifference;

That query will give you all fields of all records whose fieldVal are closest to 13. In case of ties, they all get returned--a tie breaker to return just 1 record requires one more subquery