Is date in between

[QB]

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

 

[John Spencer]

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