Isolate the datalines based on a given date within a date interval

[mr_jules]

Hi All

I have the following dataset:

Itemnumber
Start date
End date
Pricelist name
Price

My challenge is to write a Query, that will give me the Price on any given date.
I would like the Query to ask me what date the returned Price should be based upon. For example i'll punch in 20170215 and the Query will automatically return the Price(s) where this date falls within the date interval.

I have attached a data example to illustrate my point...

Hope to hear and lean from the wiser...

Jesper

[mike7952]

This should do it... Assuming your Database table is named Table1

Please Login or Register  to view this content.

[mr_jules]

Hi Mike

This doesn't quite work as far as I can see...

The date I punch in should be decisive for what Price is returned. So, if I have a Price of 5$ valid in the period 20161001 to 20171231 and another Price, 6$ valid from 20160901 to 20160930, and I punch in 20160914 then the Query must identify the right Price based on the date interval - in this case the punched date falls into the latter interval (20160901-20160930) and the Price valid on the date 20160914 is Thus 6$...

Does that make any sense?

[alansidman]

Does this work for you?

SELECT table1.Itemnumber, table1.[Start date], table1.Pricelistname, table1.Price, table1.[End date]
FROM table1
WHERE (((table1.[Start date])<[Search Date]) AND ((table1.[End date])>[Search Date]));  

[mr_jules]

Yes, that's what I needed!

Thanks very much!