[AccessD] Filter for #2 in SELECT TOP 2 qry criteria

A nested query and a reverse sort is what you need...

In (select top 1 EventStartDate From   
(SELECT TOP 2 EventStartDate FROM tblEvents WHERE EventStartDate > Date()-10 And EventName = "xyz" 
ORDER BY EventStartDate DESC) 
)

should do it.

Lambert


-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William Hindman
Sent: Friday, November 20, 2009 10:57 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Filter for #2 in SELECT TOP 2 qry criteria


"In (SELECT TOP 1 EventStartDate FROM tblEvents WHERE EventStartDate > Date()-10 And EventName = "xyz" ORDER BY EventStartDate)"

I use the above query criteria to derive the current (upcoming) EventID

Now I need to filter for the "next" event, the one that would be added if I used "SELECT TOP 2" in the above criteria instead.

Any ideas appreciated.

William 


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com