Lookup value based on nearest date

[Carol Chisholm]

I have a table of rates which occur on certain dates (one a month).
It has two fields Rates.Date and Rates.Value

I then another table where each record has a date in it, and I need to
find the Value from Rates at the matching date, OR at the nearest date
if there is not an exact match.
It is possible that the date I need to lookup is before or after the
range of dates in my Rates table.

Is there a nice elegant way of doing this?

 

[Jeff Boyce]

Carol

I believe both of those fieldnames ("Date" and "Value") are reserved words
in Access. I'm certain about the first and confident about the second.
Since Access reserves the use of these words for one meaning, when you name
a field Rates.Date, Access decides on its own whether to use your definition
or its own. Change the names of these fields.

If you use a query, you could use the "less than" or "greater than" symbols
("<" or ">") to compare your date values.

Regards

Jeff Boyce
Microsoft Office/Access MVP