Re: Query that will return date closet to another

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

You can create a query with a calculated field like:

Abs(DateDiff("d",[dtmDate],DateX))
or simply use the expression ABS(DateX - [dtmDate])

The closest one can be found via the minimum value of the result. Do you
know how to do this in a visual query? You can do this in two queries if
it's easier. Then change the query properties to return the TOP 1 value when
you sort ascending. You'd have to add more logic in case of a tie if it
matters.


woods1119 wrote:
i need a query that will return the date (in my case the date of a sample)
closest to date X.

these are my fields: ingAutoNumber, strSerum#, dtmDate (this is the date i
need it to return closest to date X)

i do not know SQL that well so any help would be appreciated. thanks!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200611/1

.

Relevant Pages

  • Re: How to find closest point(s) around a specific point in point cloud?
    ... Let's say I wanted to pick the 5 closest points around a specific ... one scans the cloud, calculating the distance to each point, and keeping track of the closest matches. ... this may infact be the best approach if one is doing a "one-off" query. ... a spherical query is also possible, but is problematic (the size of the sphere is very important, but may not be known prior to the query, which would require iteration). ...
    (comp.programming)
  • Re: i know its a floating-point imprecision...
    ... Where Query ... Analyzer displays the closest 17-digit decimal value to the stored float, ... You might store a float like piand see what each of these shows you. ... >I understand that the value is stored in SQL Server using floating points. ...
    (microsoft.public.sqlserver.server)
  • Re: Using DLookUp as the default value of a Forms ComboBox
    ... based on a DLookUp to a query. ... I can do this in Excel as follows, but can't get DLookUp to do it: ... and it returns the closest value from column 1 of that data range. ...
    (microsoft.public.access.formscoding)
  • how to get wifi transmission rate?
    ... How do you get the current transmission rate? ... The Microsoft API does ... The closest I could get was an ... OID_802_11_SUPPORTED_RATES query. ...
    (microsoft.public.windowsce.embedded)
  • Re: Im sure this is really simple!
    ... You usually can't use a calculated field in a further calculation. ... You tried to execute a query tthat does not include the specified expression ... Please post the complete SQL of the query. ...
    (microsoft.public.access.queries)