Nearest day in parameter query

[Dwight]

Hello:

This should be simple but I couldn't find a similar thread.

I have a table that contains values by date. Using a parameter query I want the user to enter a date and the database will pull the value from 2 years ago. I am using this as a criteria DateAdd("yyyy",-2,[DateSelect]) in the query.

It works fine except when there is not a corresponding date exactly 2 years ago. My question is how do I get it to take the nearest date?

For example, if someone entered 12/21/2004 my criteria would look for 12/21/2002 but there may not be a value for that particular day. However, it should be smart enough to use 12/20/2002's value. I know I can't use the LIKE wildcard functions (because my dates are not text strings) but don't know what I can use.

Thanks

 

[Jon K]

Type/Paste in the SQL View of a new query, replacing with the correct DateFieldName and TableName:-

SELECT TOP 1 [DateFieldName]
FROM [TableName]
WHERE [DateFieldName]>=DateAdd("yyyy",-2,[DateSelect])
ORDER BY [DateFieldName];

.

 

[Dwight]

Thanks Jon K. That works well and I was not aware of the Top function before you called my attention to it.