I have a table of commodity prices and price dates and another table with supplier transaction data that also has a column with dates. The price dates and supplier dates column are not identical. If I pick a date from the supplier table, I want to retrieve the commodity price of the date closest to that supplier transaction date. The commodity price date can be before or equal to the supplier transaction date but not later than this date.
[List3 is the possible supplier dates and List4 is the commodity]
SELECT TOP 1 CommPrices.Price
WHERE (((CommPrices.DateComm)<=[Forms]![Tool Should Cost]![List3]) AND ((CommPrices.Commodity)=[Forms]![Tool Should Cost]![List4]))
ORDER BY CommPrices.DateComm DESC;
TOP 1 and
SELECT TOP 1 CommPrices.Price FROM CommPrices WHERE CommPrices.DateComm <= [Forms]![Tool Should Cost]![List 3] ORDER BY CommPrices.DateComm DESC;