Querying Based on Historical Price

[CT Witter]

I'm having a tempory loss of how to accomplish this. I have a table that has equipment id, cost and start and end date. I want to obtain the cost at a certain point in time based on the start / end date and the date the user provides.

Below is a copy of my table.

cost.xls
	A	B	C	D
1	EquipID	COST	StartDate	EndDate
2	1	$1,200.00	1/1/2005	12/31/9999
3	2	$1,500.00	1/1/2005	1/15/2005
4	2	$1,700.00	1/16/2005	12/31/9999
5	4	$1,700.00	1/1/2005	12/31/9999
6	5	$1,900.00	1/1/2005	12/31/9999
7	6	$2,400.00	1/1/2005	12/31/9999
Sheet1

The user provides a date ie 1/13/05 for equipment id 2, the cost should be 1200. If they provided 1/16/05, then cost would be 1700.

Should I set up my table differently?

Thanks,
CT

 

[cstlaurent]

With a query it would look like that.

Select cost from table where Date > startdate and date < Endate.