CODEWITHSUNDEEP
sqlvbams-access
Ben G
Ben G

Reputation: 105

How do I write this SQL query in MS Access to

I have a list of records like so

ID---EffectiveDate---Rate
1----1/1/2011--------1.2
2----1/1/2012--------1.3
3----1/1/2013--------1.5
4----1/1/2014--------1.2

Given a date parameter, d1, I want to get the record with the latest effective date prior to d1. So, if d1 = 6/1/2012, I want to get the second record. How can I write a query like this in MS Access SQL?

Upvotes: 0

Views: 1121

Answers (2)

Vijaysing Gusinge
Vijaysing Gusinge

Reputation: 102

Try this,

select top 1 * from tablename where EffectiveDate<=d1 order by EffectiveDate desc

Upvotes: 1

OptimizedQuery
OptimizedQuery

Reputation: 1262

I think the SQL query would looks something like this, based on your data:

SELECT TOP 1 EffectiveDate
FROM MyTableOfDates
WHERE EffectiveDate <= #MyInputDate#
ORDER BY EffectiveDate DESC

Upvotes: 1

Related Questions