Select query using a date field

[Guest]

I have a table in which each record has a date associated with it. I need to
extract the record that has a date closest to a date entered on a form.
Specifically, it needs to be the record that is the highest in the table but
lower than or equal to the date on the form e.g. if the table contains
records with 20/01/2006, 20/2/2006 and 20/03/2006 and 01/03/2006 is entered
to the form, then I want to extract the record dated 20/02/2006.

How can this be done?

 

[John Spencer]

You need to use the TOP predicate in a query. A generic example follows

SELECT TOP 1 SomeDate
FROM SomeTable
WHERE SomeDate <= Forms![YourFormName]![YourDateControl]
ORDER BY SomeDate Desc, [PrimaryKeyField in SomeTable]

TOP can return more than one record if there is a tie in the sort order. If
you want to avoid ties then your sort order should have fields in it that
will force a unique sort order. In one table queries that would be the
field(s) making up the primary key.

If you are trying to do this in the grid, you can set TOP using the query's
property sheet.