Query that will return date closet to another

[Guest]

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!

 

[kingston via AccessMonster.com]

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.

 

[Guest]

First create a table named CountNumber with a field named CountNUM having
numbers 0 (zero) through the maximum spread you think you may need.
Try the query below --
SELECT woods1119.[strSerum#], First(woods1119.dtmDate) AS dtm_Date, [Enter
DateX] AS [Date X]
FROM woods1119, CountNumber
WHERE (((woods1119.dtmDate)=DateAdd("d",[CountNumber].[CountNUM],[Enter
DateX]) Or (woods1119.dtmDate)=DateAdd("d",-[CountNumber].[CountNUM],[Enter
DateX])))
GROUP BY woods1119.[strSerum#], [Enter DateX];