Dmax - to return not quite the highest

[SteveC24]

Hello,

I am using DMax() to return the highest date in a table, but really need it to return the second-highest...so, if I had:

14th Feb
10th Dec
1st Jan

I don't want it to return 10th Dec, I want it to return 14th Feb

Any help much appreciated. I am sure there was something like dmax()-1, but that just returns (in my example above) 9th Dec!

Thanks!

 

[Rickster57]

Dates

First, you may run into issues later if you don't have a true Date Field with a year

Create a query (call it query 1)using your table and date field. Set the Sort to Descending Order in the date field. Next to the Sigma button in your toolbar up at the top enter 2 in the drop down box. This will give you the top 2 in the table. You probably will have to delete the 5 in 25 since it will try to use that.

Run the query.

Now make another query (call it query2)that uses the above query(query 1) as its Data Source. Set the Sort to Ascending and choose 1 in the drop down box next to the Sigma button.


I don't believe there is any quick and easy way to do this. You could also use an update query but that isn't much easier.

Hope this is clear

 

[SamDeMan]

you can use Dmax(Format(MyDate, "ddmm")) you may also need to use a CInt Funtion to covert the date to a number.

 

[SteveC24]

Sorry - as usual, my hideously poor example has caused more problems than it helped!

I do have proper date fields, that store the whole date, I just figured putting a year in my original post would help only to confuse things further! Sorry!

It is a real pain that there is no direct way of doing this.

I have a "StockCheck" table, and a "StockCheckItems" table. The point of this question was so that it looked up the stock expected to be available and displayed it on the currently running stock check. It got this by looking at the "StockCheck" table, and just selecting the most recent date, but the most recent date would always be today (as in, todays stock check), when I actually want last weeks, or last months, whatever the most recent was (but not today).

I get the impression I am still making no sense...so I shall finish on my intended solution!

I plan to add another field to the stockcheck table, which indicates if the stockcheck is COMPLETE - so I can then, on future stockchecks look for the most recent date, which is ALSO marked as completed.
This will also allow various other checks, and to finalise the data entered etc.

Thank you for your patience, brainpower and other miscellaneous help!

 

[Brianwarnock]

Maybe I'm missing the point but can't your query use a having clause similar to below

HAVING (Max(Table1.flddate)<Date());

Brian