Query needed to return last, or next to last, value based on date...

[RocketRod]

I have the following tables: Rates and SelectedDates, which are structured as follows.

Rates: RateID, EffectiveDate, Rate
SelectedDates: Date. Table is bound to a combobox in which user selects one or more dates, mm/dd/yyyy, which is the same format as Rates.EffectiveDate.

I need a query to return the first Rates.Rate for the following conditions. (there will be a minimum of one record in the Rates table)

IF there is only one Rates.EffectiveDate for the SelectedDates.Date
return the associated Rates.Rate

ELSE IF count(Rates.EffectiveDate <= SelectedDates.Date)>=1
return the first Rates.Rate where Rates.EffectiveDate <= SelectedDates.Date

ELSE
return Rates.Rate associated to the Rates.EffectiveDate closest to the SelectedDates.Date.


I hope this is clear, as it is late and I am tired...

Thanks in advance,
Rod

 

[Ryko]

Perhaps you could use the DateDiff function.

The one record in Rates table with lowest non-negative DateDiff("d", Rates.EffectiveDate, SelectedDates.Date) is what you're probably looking for, assuming you don't want to return rates with effective date past selected date.

If two or more rates have the same effective date you'll have to use another criteria, like lower/higher RateID or Rate value. Not sure I completely understand your conditions, though.