Finding values based on dates in a query

[ebasshead]

Hi Everybody,

I'm having problems getting my head around finding a value (price) based on a date range. I have created a table which lists product items, price, and date (that the price will come into effect). Some products have more than one price and date.

What I would like to do is, select the product, select, for example a random date, and the query will find price that the product is valued at, at that time.

I've been using the IIf function in queries like...

IIF([DateCosted]>#01/02/2009# And [Product]="House",350,IIF([DateCosted]>#02/03/2010# And [Product]="House",450,200)) etc

But now I've reached a wall and have to do it another way

I have super limited knowledge of VBA, so I'm hoping the Grandmasters can possibly guide me to another method.

Cheers Eddie

[NeoPa]

You say VBA rather than query. What are you hoping to do with the returned value?

[ebasshead]

Hi Neo,

I'm wanting to use the value returned for calculations in another query, ultimately to populate a form and report

Cheers Eddie

[NeoPa]

I expect you'd need to link your tabe into a grouped subquery that finds the max date which is <= to that specified on your form or entered by the operator, depending on how you do that bit. Something like :

SELECT   [Product]
       , [Price]
       , [DateCosted]
 
FROM     [tblProduct] AS tP INNER JOIN
    (
    SELECT   [Product]
           , Max([DateCosted]) AS [MaxDate]
    FROM     [tblProduct]
    WHERE    [DateCosted]<=[Enter Date or Get From Form]
    GROUP BY [Product]
    ) AS subQ
  ON     tP.Product=subQ.Product
 AND     tP.DateCosted=subQ.MaxDate

[ebasshead]

Thanks so much Neo,

I think setting the date costed as max date is the piece of logic Im missing

Cheers Eddie

[NeoPa]

A pleasure. An interesting question :)