Re: Find Nearest Value

From: Allen Browne (AllenBrowne_at_SeeSig.Invalid)
Date: 09/29/04 

Date: Thu, 30 Sep 2004 01:15:55 +0800

Try entering something like this into the Field row in query design:
([ID] = (SELECT First([ID]) FROM MyTable AS Dupe ORDER BY
Abs(Dupe.[Amount] - 475)))

It may show 0 for false, and -1 for true. 

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Tom" <anynomys@newsgroup.com> wrote in message
news:uP73oNkpEHA.3524@TK2MSFTNGP15.phx.gbl...
> Allen,
>
> thanks for the info... this didn't give me the TRUE/FALSE though.
>
> Any ideas as to how I can implement it?
>
> Tom
>
>
>
> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
> news:#AxjgojpEHA.1992@TK2MSFTNGP09.phx.gbl...
>> What you are asking for is the minimum absolute difference:
>>     SELECT First([Amount]) FROM MyTable ORDER BY Abs([Amount] - 475);
>>
>> You could use that kind of expression in the context of a query as a
>> subquery. If you need something in VBA code, DLookup() won't work because
> it
>> lacks the ORDER BY argument, but this replacement will:
>>     http://members.iinet.net.au/~allenbrowne/ser-42.html
>>
>> --
>> Allen Browne - Microsoft MVP.  Perth, Western Australia.
>> Tips for Access users - http://allenbrowne.com/tips.html
>> Reply to group, rather than allenbrowne at mvps dot org.
>>
>> "Tom" <anynomys@newsgroup.com> wrote in message
>> news:eV9B0cjpEHA.1644@tk2msftngp13.phx.gbl...
>> >I need some help with finding the "nearest" value and add TRUE/FALSE 
>> >into
>> >an
>> > expression field.
>> >
>> > This is the data as pulled from the query:
>> > ==========================
>> >
>> > ID     Amount
>> > 1       100
>> > 2        99
>> > 3       510
>> > 4       450
>> > 5        21
>> > 6      1000
>> > 7       375
>> > 9       395
>> >
>> >
>> > I want to find the "Amount" that is nearest to "475".
>> >
>> > The "direction" can be either "above" or "below" 475.  In this case,
> "450"
>> > is TRUE (below amount).
>> >
>> >
>> > This is the data as I want to see it when qry is executed:
>> > ====================================
>> >
>> > ID     Amount          Expression
>> > 1       100            False
>> > 2        99            False
>> > 3       510            False
>> > 4       450            True
>> > 5        21            False
>> > 6      1000            False
>> > 7       375            False
>> > 9       395            False
>> >
>> > If the number to be found was "490", I would expect "510" to be TRUE
> (and
>> > 450 to be FALSE).
>> >
>> >
>> > Does anyone know how to do that?
>> >
>> >
>> > Thanks,
>> > Tom
>> >
>> > P.S. As shown above, the records are sorted by ID

Relevant Pages

  • Re: How can I extract the address from and implrted hyperlink
    ... acAddress) did ... query, and type an expression like this into the Field row in query design: ... Allen Browne - Microsoft MVP. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Currency without decial
    ... Create a query into this table. ... enter something like this into the Field row (assuming your ... currency field is named Amount): ... Allen Browne - Microsoft MVP. ...
    (microsoft.public.access.queries)
  • Re: Compare Addresses
    ... Create a query with the 2 tables, and no join line between them. ... In the Field row, enter: ... In the Criteria row under this, ... Allen Browne - Microsoft MVP. ...
    (microsoft.public.access.queries)
  • Re: Deleting all records with a Wednesday date
    ... Lee ... Create a query into this table. ... In a fresh column in the field row, ... > Allen Browne - Microsoft MVP. ...
    (microsoft.public.access.queries)
  • Re: need help creating criteria for time
    ... Allen Browne - Microsoft MVP. ... I then removed the criteria and left everything else and ran it ... Now let's verify that these date/time fields have *only* date or time, ... If you switch your query to SQL View (View menu, ...
    (microsoft.public.access.queries)