Previous Date

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

I'm looking for how to set up a query to return the record with the previous
date to the most recent date. Example:
ReturnDate:
11/15/03
12/12/03
2/4/04
4/5/04

The most recent date is 4/5/04. I need to return the record with 2/4/04.

Thanks!

Mark
 
Hi

The SQL like:
SELECT * FROM MyTable AS a
WHERE (((a.DateField) In (SELECT MAX(b.DateField) FROM MyTable AS b
WHERE b.DateField NOT IN (SELECT MAX(c.DateField) FROM MyTable c))));

will do it, but unless MyTable is small enough, such queries take a lot of
time in Access. It my help, when you save the query for pre-last date as
separate query

qPreLastDate=(SELECT MAX(b.DateField) FROM MyTable AS b
WHERE b.DateField NOT IN (SELECT MAX(c.DateField) FROM MyTable c)

Now your query will be
SELECT * FORM MyTable As a WHERE a.DateField IN qPreLastDate
 
Mark,

From your phrasing I understand you want to set up a query in design view,
not programatically... which means ve posted to the wrong newsgroup. Anyway,
here it goes:

Make a simple select query on your table. Set the sort order on ReturnDate
to Descending, type the following criterion under ReturnDate:
< DMax("[ReturnDate]","TableName")
substituting TableName with the actual table name, and right-click in the
upper half of the design view (source tables area) to open the query
properties. Change the Top Values property from default All to 1.
You should be ready.

HTH,
Nikos
 
Back
Top