Get Dates from Table that are 1 date record later than today

[manasi]

I have a table(Weeks) for storing Weekend and Monthend Dates for Timecards, containing data as:
04/12/2003
04/05/2003
03/31/2003
03/29/2003

Now, based on today's date, I need to retrieve the immediate Weekend/Monthend following today from this table. How do I do it? I am running into a problem when today's date is close to a monthend.
For, instance: if today's date is 03/28/2003, and I run this query -

select * from Weeks
where DateField > today and today <= Datefield + 7;

I get 03/29/2003 and 03/31/2003 as the result. But I only want to get 03/29/2003.
Pl. note that this query works when the date is o.w. and always gives me the immediate Weekend following today's date.
Please help asap!!!!!

 

[Spellman]

Try something like

select Min(Datefield) from Weeks
where DateField > today and today <= Datefield + 7;


Mark

The key to immortality is to make a big impression in this life!!

 

[manasi]

Thanks for the Min tip. I tried the query like u wrote and added a Group By to it, since it wouldn't work without a GROUP BY clause.
But, even after putting the clause, it still returned 2 rows, not the minimum.
So, I tried a workaround as:
Select min([week ending]) as Weekend
from
Weeks
where [week ending] in
(SELECT ([Week Ending])
from Weeks
where ([Week Ending] >= #03/27/2003# and [Week Ending] <= (#03/27/2003# + 7))
group by [Week Ending])

This worked. And I wonder why? Even if it worked, its not going to be efficient.

Any ideas?