find the closest date 22/nov/2009

[Galaxiom]

Sorry, Galaxiom, but I'm not using SQL. Old fashioned, I guess. Thanks, though.

The parameters can also be entered int he designer interface.
In 2007 it is on the Query Tools sub-ribbon.

Probably on the Design menu in older versions.

The SQL isn't that hard. Just go to the SQL view and paste that line.

 

[wilpeter]

I'll give it a try tomorrow. I usually only use SQL for union queries and other complicated ones. This seemed like a simple expectation, even of 2003.

 

[wilpeter]

I think I've decided on the simplest of the ingenious offers made.
A calculated field that uses a parameter, which provides the degree of difference in days between the Datefield records and the variable date (22 Nov 2009) and limits the output to the top 3 results):

Difference: Abs(DateDiff("d",[Enter Match Date],[Datefield]))
With Sort Order Ascending and the "Top Values" toolbar box set to 3.

We had it in two threads as it turns out.