Last Date and second to last date

[davidcantor]

I need to add the last and second to last order date to a sql report. The last date is easy Max(OrderDate) but how do I find teh second to last date?

Will this work? max(OrderDate)-1 ?

[davidcantor]

I solved this using SELECT Top 2 * FROM Orders orderby [Date] descending;

[HansV]

I was composing this, but I see you already found the solution.

-------------

That would only work if the dates are guaranteed to be consecutive.
Otherwise, you can use

DMax("OrderDate", "TableOrQueryName", "OrderDate<#" & Format(DMax("OrderDate", "TableOrQueryName"), "mm/dd/yyyy") & "#")

Another option would be to create a separate Top 2 query based on the table, sorted descending on OrderDate.

[davidcantor]

DMax("OrderDate", "TableOrQueryName", "OrderDate<#" & Format(DMax("OrderDate", "TableOrQueryName"), "mm/dd/yyyy") & "#")

How do I get the top 2 distinct order dates using this code?

[HansV]

You originally posted that you already had the most recent date. The DMax expression can be used to retrieve the next most recent date.

But a Top 2 query is easier.