If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Is date in between
I have a table 'tbl_scheduled_locations' with three fields 'sl_id','sl_date'
and 'sl_loc' so basically the data is 1 12/01/2009 Detroit 2 12/05/2009 New York 3 12/17/2009 California Now I need to build a query that tells me the location based on a date. Therefore, if I used as a criteria 12/04/2009 it would return New York and if I used 12/07/2009 it would return California. How would I build such a query? Thank you, QB |
#2
|
|||
|
|||
Is date in between
The easiest query to get the date would be
SELECT TOP 1 SL_Date FROM tbl_scheduled_locations WHERE SL_Date = #2009/12/04# ORDER BY SL_Date Desc So using that SELECT * FROM tbl_scheduled_locations WHERE SL_Date in (SELECT TOP 1 SL_Date FROM tbl_scheduled_locations WHERE SL_Date = #2009/12/04# ORDER BY SL_Date Desc) Another alternative would be to use an aggregate query to return the first (minimum) date greater than or equal to the cutoff date. SELECT * FROM tbl_scheduled_locations WHERE SL_Date = (SELECT Min(SL_Date) FROM tbl_scheduled_locations WHERE SL_Date = #2009/12/04#) John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County QB wrote: I have a table 'tbl_scheduled_locations' with three fields 'sl_id','sl_date' and 'sl_loc' so basically the data is 1 12/01/2009 Detroit 2 12/05/2009 New York 3 12/17/2009 California Now I need to build a query that tells me the location based on a date. Therefore, if I used as a criteria 12/04/2009 it would return New York and if I used 12/07/2009 it would return California. How would I build such a query? Thank you, QB |
Thread Tools | |
Display Modes | |
|
|