Looking up a Rate change table

[Ian Chappel]

Say I have tblRates with the fields FromDate and NewRate.

What's the easiest way to obtain the Rate on a particular day, which could
be between two FromDates or >MaxOfFromDate? I'd rather not have a ToDate
field.

This could take the form of a query, or a DLookup.

 

[[MVP] S.Clark]

To use a query, it will be much easier to implement, if you have the end
date. Otherwise, you have to find the next date in the series, then react.
It just adds an extra step to the code, whereas it takes two seconds to
enter.

 

[Marshall Barton]

Say I have tblRates with the fields FromDate and NewRate.

What's the easiest way to obtain the Rate on a particular day, which could
be between two FromDates or >MaxOfFromDate? I'd rather not have a ToDate
field.

This could take the form of a query, or a DLookup.

You did not state the context where you want to do this, but
this kind of query should get you started:

SELECT TOP 1 NewRate
FROM tblRates
WHERE FromDate <= anydatevalue
ORDER BY FromDate DESC

 

[Ian Chappel]

Thanks Marsh, that's just what I wanted. I don't like the idea of inputting
a ToDate, as it seems unnecessary, and overlapping periods could be created
unless data is carefully validated.

I'm not sure why the first reply felt there should be a ToDate?

 

[Marshall Barton]

I haven't checked lately, but I think TOP is not a standard
ANSI SQL predicate. Without TOP this can be a rather messy
thing to do and it's often not worth the effort if you need
to use a query that's portable to other database engines,
which may very well be Steve's point of view.