Match multiple criteria

[Franky27]

I cant find out next

I have a table like this

Product from till price
1 2007-01-01 2008-12-31 10
1 2008-01-01 2009-12-31 11


In a query I want to match this

Product date price
1 2007-01-14 10
1 2007-01-18 10
1 2007-01-20 10
1 2008-05-08 11
1 2010-05-15 11
1 2010-08-09 11


That means when the date in the query match between the from and till. I want the price from that colomn. Ok to do this thats not the problem but.

If the date of my query is not in the range of my table I want the price of the range with newest range

so date 2010-08-09 should give me the price out of the table 1 2008-01-01 2009-12-31 11
Because the "till"date is most nerest to the date in my query

Who can help me?

 

[vbaInet]

I don't understand when you say "if the date is not in the range". Can you explain further?

Sounds like you want the price with the max date that corresponds to the Product ID.

 

[Franky27]

Ok

I mean.

I want to find the price in this query

Product | date| price
1 2010-08-09 ?

get price out of this table

Product | from | till | price
1 | 2007-01-01 | 2008-12-31 | 10
1 | 2008-01-01 | 2009-12-31 | 11


august 8th 2010 is not in 1 of the from - till range so I want the price of the one with newest date

Hope this will be clear thanks

 

[vbaInet]

Something like:

IIF(Nz([Price], "") = "", (SELECT TOP 1 Q.Price FROM [COLOR=Red][B]TableName[/B][/COLOR] as Q WHERE Q.Product = [COLOR=Red][B]TableName[/B][/COLOR].Product ORDER BY Q.TillDate DESC), [Price])

Substitute the right field names and tablename.

 

[Franky27]

thanks a lot vbaInet,

Great help. Im working it out now

 

[vbaInet]

No worries! Let us know it you got it working.