Sort query results by closest number?

[Alan Ibbotson]

5 records have a number field:

recordA = 10000
recordB = 500
recordC = 25000
recordD = 16757.8
recordE = 20000

My sort critera will be different everytime but always a number. Lets say
this time the critera is 15000.

I would like my query results to list the closest number field to my critera
1st and thefarthest number last. The results would be:

recordD = 16757.8
recordA = 10000
recordE = 20000
recordC = 25000
recordB = 500

I hope that's clear and TIA.

 

[Jeff Boyce]

Alan

That sounds like you want to sort on the difference, not on the number. If
you add a calculated field to your query, take the Absolute value of the
difference between your reference criterion and the values in your table,
then sort on THAT number, I suspect it would work.

Good luck

Jeff Boyce
<Access MVP>

 

[Marshall Barton]

5 records have a number field:

recordA = 10000
recordB = 500
recordC = 25000
recordD = 16757.8
recordE = 20000

My sort critera will be different everytime but always a number. Lets say
this time the critera is 15000.

I would like my query results to list the closest number field to my critera
1st and thefarthest number last. The results would be:

recordD = 16757.8
recordA = 10000
recordE = 20000
recordC = 25000
recordB = 500

Create a calculated field in the query using an expression:
Expr1: Abs(numberfield - [Enter Sort Number])

Uncheck its Show box and set the Sort to Descending.