How to identify a row with a date range based on a specific date

[#1]

Hi All,

I'm new to the product and after scratching my head for a few hours am hoping for a few pointers in the right direction.

I have a product (actually energy) which is purchased at different rates during the year, the data I have (tarriff) shows me the date range which was in effect for each price, e.g. ....

Product, price, startdate, enddate

ELEC, 0.05, 01/01/10, 31/3/10

ELEC, 0.065, 1/4/10, 31/11/10 etc.

I have a usage table, which tells me the date each product was used e.g.

Product, dateused, Quantity

ELEC, 05/01/10, 49.3

ELEC, 25/6/10, 23.9 etc.

I am trying to find a way of adding to the usage table the price paid, which is stored in the tariff table so I end up with

Product dateused, Quantity, Cost

ELEC, 05/01/10, 49.3, 2.465

ELEC 25/06/10, 23.9, 1.5535 etc.

I've tried several different ways of trying to achieve this and am hoping for someone to shed some light on the best approach to deal with the challenge.

Thanks in advance

[matt_crowther]

That looks like a job for 'IntervalMatch()' - have a look in the Help file as the examples are far more useful than anything I could post!

Hope that helps,

Matt - Visual Analytics Ltd

[#3]

Thanks Matt, that's a new one on me and seems to do what's required, I'll give it a go later today.