Compare numbers

[Abrm]

Hi ,

I am making a comparison with 3 numbers in many records
Nr1 is a static number
Nr 2 and 3 are variabel.

I would like to get the number that is the closed by Nr1.

For Example NR1 =10, Nr2 =8, Nr3=13
10-8 =2
10-13= -5
Nr2 is then number I get.
Is there a function to do these kind of comparsons?
Or another way?

Abrm

 

[Al Campagna]

Abrm,
= IIF(Nr1-Nr2 < Nr1-Nr3, Nr2, Nr3)
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[John Spencer]

You might use an expression that looks like

IIF(Abs(Nr1 - Nr2) < Abs(Nr1-Nr3) , Nr2, Nr3)

If any of the numbers are null (blank) then the above expression will
require modification to handle that. The modification would be dependent on
what rules you wanted to apply.

For instance if Nr1 is null, do you want the value of the number closest to
zero, null, or one of the numbers (nr2, nr3) returned.
IF nr2 or nr3 is null do you want to return the other number or null

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..