Pull the 5th value from a table

[Bullsandbears123]

I have a table that I would like to pull the 5th most recent date. The table is a table with dates

example:

[date]
10/30/2003
10/29/2003
10/28/2003
10/27/2003
10/24/2003 <---I need this date
10/23/2003
10/22/2003
As you can see, the table is missing a few dates. Is there a way to get this.

Thanks

 

[oharab]

You will have to do 2 queries: The first to get the top 5 dates, then a second based on that to get the last date in it!

hth

ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

&quot;Where are all the stupid people from...
...And how'd they get so dumb?&quot;
NoFX-The Decline
----------------------------------------------

 

[oharab]

My mistake, that won't work, you can't get the last one directly. The second query will have to order the dates in the reverse order, then take the first one!

hth

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

&quot;Where are all the stupid people from...
...And how'd they get so dumb?&quot;
NoFX-The Decline
----------------------------------------------

 

[Bullsandbears123]

Thanks for the help, I ended up using
Dmax(date) with criteria of [date]<=dmax(date)-5

not the actual code but you get the idea.

THANKS AGAIN

 

[jrbarnett]

You could use a DAO Recordset and retrieve the 5th record.

John

 

[DeltaV]

Yeah, Create a Sub that Opens a Snaphot of the table, use VBA to move down 5 records.

Use the MoveFirst and MoveNext commands.

DV

 

[Bullsandbears123]

I tried this, but I keep getting a &quot;runtime error: 3021, no current record&quot; error message,

I don't know why, b/c when I check the record count it states 5000 records, which is correct. Any ideas?

Dim intcnt As Integer

Dim db As DAO.Database
Dim rst1 As DAO.Recordset

sql1 = &quot;Select &quot; & table & &quot;.Appdate FROM &quot; & table & &quot; ORDER BY &quot; & table & &quot;.AppDate DESC;&quot;

Set db = CurrentDb()
Set rst1 = db.OpenRecordset(sql1, dbOpenSnapshot)

intcnt = 1
rst1.MoveFirst
rst1.MoveLast
rst1.MoveFirst

MsgBox (rst1.RecordCount)

Do While intcnt <= intday
rst1.MoveNext

intcnt = incnt + 1

Loop

getrundate = Nz(rst1.Fields(&quot;AppDate&quot, Date)

 

[jrbarnett]

The variable &quot;intday&quot; has not had a value set, so unless this is set earlier, you are comparing null with intcnt which doesn't make sense.

John

 

[Bullsandbears123]

it was set earlier, but I figured it out, this is the code I'm using...

Function Getrerundate(Optional table As String = &quot;ExpPosHistory&quot;, Optional intday As Integer = 5) As Date


Dim maxdate As Date

maxdate = DMax(&quot;appdate&quot;, table)

For i = 1 To intday - 1
maxdate = DMax(&quot;[Appdate]&quot;, table, &quot;[AppDate]<#&quot; & maxdate & &quot;#&quot
Next

Getrerundate = maxdate