search a value with 2 criterias

[Patougaffou]

Hello Everybody,

I would like To make a query that search a price from 2 criteria. If someone could help me please.

the table of cost price

DATE        CODE    Article      Price       
01/02/2011   codeA   articleA   789,45
07/02/2011   codeA   articleA   750,02
11/02/2011   codeA   articleA   724,99
01/05/2011   codeA   articleA   773,25
01/06/2011   codeA   articleA   820,03

the query that will search for the first criteria 'codeA' and second criteria the date with is the closest date before the invoice date and display the cost price.

InvoiceDate    CODE    Article      Price
05/02/2011    codeA    articleA    789,45
15/02/2011    codeA    articleA    724,99
01/05/2011    codeA    articleA    773,25
18/06/2011    codeA    articleA    820,03

thanks a lot for your help

[rpeare]

Does the article have anything to do with it? You mention only the code or is the code the code for articleA

[Patougaffou]

The codeA is for articleA it could have several code but I just mention one for the example.

[rpeare]

The only way I can see to do this is with a nested d function which is going to be extremely inefficient if your dataset is large. Is there a reason you're not storing the original price, or at the very least the original price pimary key if it's important for you to retrieve at a later date?

This query will work but as I said it's going to be very inefficient.

SELECT Tbl_Activity.InvoiceDate, Tbl_Activity.Code, Tbl_Activity.Article, DMax("[PriceDate]","Tbl_ArticlePrices","[PriceDate] <= #" & [invoicedate] & "#") AS MRDate, DLookUp("[price]","Tbl_ArticlePrices","[pricedate] = #" & DMax("[PriceDate]","Tbl_ArticlePrices","[PriceDate] <= #" & [invoicedate] & "#") & "#") AS Expr1
FROM Tbl_Activity;


I named your first table Tbl_ArticlePrices
I named your second table Tbl_Activity

You can adapt this to your code, but I would really recommend you store the original price (better yet the original price primary key) in your activity table so it is always there for easy reference.

[Patougaffou]

I' sorry I may be didn't explain it very well because I am a french speaking

The second list is not a table but a query and I would like to have a formula to display as shown, the cost price.

Thanks a lot for your support.

[rpeare]

It doesn't matter if one is a table and one is a query if you use the example query I set up it will work.