Most recent date query?

[kilobyte]

I need to know if it is possible to make a query that selects the highest or lowest value below or above a number that a user or form defines.

For example, the attached database has a history of which of three children held the position of mom's favorite by recording the day that they became mom's favorite, the idea being that they stayed there until someone replaced them.

It is possible to make a report that would request a date from the user and then would return with who was mom's favorite on that date? Perhaps by checking the records to see which record had the most recent date before the date entered.

Just in case you haven't noticed, this is not the practical application of this concept, I am just trying to find out if it is possible.

 

[Murli]

Heres a sample:

I could do it using three queries. May be theres a simpler way that i am not aware of.

Just go the the form1 in the attached DB and enter the date and click the command button.

Let me know if that helps.

 

[kilobyte]

Murli,

This in ingenious. I don't know how you came up with that.

Still though, there is a problem. The queries calculate the closest date to the one entered, not the closest date before the one entered.

For example, if you put in 12/12/2005 it comes back with Betty, who's start date is 12/13/2005. 12/12/2005 should still be Joe's territory.

Any ideas?

 

[kilobyte]

Done!

I found a way to make a query that works.

SELECT TOP 1 momsfav.MomFav, momsfav.StartDate
FROM momsfav
WHERE (((momsfav.StartDate)<=[Forms]![form1]![Text0]))
ORDER BY momsfav.StartDate DESC;

As you can see, it displays dates equal to or before the date entered, sorts them decending (most recent at the top), and then only displays the top value. Kinda cheap, but it works.

Thanks for the help!