Query to return the record with the date immediately previous to chosen date

[crimedog]

I have a Table with Date; Person; Manager; Quota
A person will/may have several records.
For Example on
1/1/2010 Joe has manager Paul and quota 50
1/1/2017 Joe has manager Sam and quota 100
4/1/2017 Joe has manager Jim and quota 500

I open my Query for February 2017 - I want it to return Joe has manager Sam and quota 100
But if I open Query for April 2017 - I want it to return Joe has manager Jim and quota 500
if I open Query for December 2016 - I want it to return Joe has manager Paul and quota 50




So I want to return / compare against historical information Choose Month(1-12) Choose Year - return the record from the date closest to but not past (like the Price is Right)

What is the filter for Date?

[ranman256]

set the limit on the data given on the form, then return the max date :Q2

Q1 :select ID, Max([Date]) from table where [date]< forms!myForm!txtDate
Q2: select * from table where Q1.ID = Q2.ID

[Micron]

maybe
SELECT TOP 1
SomeDate, Person, Manager, Quota FROM tblMyTable
WHERE SomeDate < Date
ORDER BY SomeDate DESC;

Date is a reserved word and should not be used for any db object or field. I don't know what you'd get if you wrap your field name in square brackets [Date] but I'm pretty sure Access will return nothing if comparing WHERE Date < Date since both should return today's date given that Date is a function.