Using dates in criteria

[Jim]

I am putting citeria in a query for a table. The table has values associated
with dates that are both less than and greater than today. I would like to
set a criteria that says, "If there is a date that is greater than today then
return the value for that date as well as a value for only the last date
before today. If there is no date greater than today then return the value
for only the last date befoe today."
Example table
Item Date Value
123 10/1/2005 40
123 12/1/2005 20
345 9/1/2005 30
345 8/1/2005 25

In the above table Item 123 has a future value and a past value so I would
like to see both. Item 345 has no future value but multiple past values. I
only want to see the most recent past value.

Thoughts? Thanks for your help.

 

[John Spencer]

One method might be

SELECT ITEM, Example.[Date], [Value]
FROM Example
WHERE Example.[Date] > Date()
OR [Date] IN
(SELECT Top 1 Tmp.[Date]
FROM Example as Tmp
WHERE Tmp.Item = Example.Item
And Tmp.Item <Date()
ORDER BY Tmp.Date DESC)