CODEWITHSUNDEEP
sqlms-access
John
John

Reputation: 69

MS Access Select Record with the Date closest to Todays Date

I am writing a SELECT query in MS Access. There is a child table (one to many) where each person has multiple records with different EndDate like the following:

Schedule Table
ID   StartTime    EndTime   AssignmentEndDate
1    6:00         12:00     01/01/2016
1    6:00         12:00     06/30/2016
1    6:00         12:00     01/01/9999

From the record, there are 3 AssignmentEndDate records. I need to fetch the record with the Date closest to Today's date, but not passed it yet. Today is 06/13/2016 so, I need to fetch the 2nd record with AssignmentEndDate 06/30/2016. If today is 7/13/2016, it would be the record with Date of 01/01/9999.

I can't use Max()... So how can I write a SELECT that will do this?

Thanks

Upvotes: 0

Views: 1392

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

You can do this as:

select top 1 s.*
from schedule as s
where assignmentdate > Date()
order by assignmentdate asc;

Upvotes: 2

Related Questions