How would I parse out an array to get the current price of a product that has multiple prices based on Effective and End dates?

ie...

Price file

Effective End Price

1/01/2005... 12/31/2005 $31.00

1/01/2006... 12/31/2006 $31.50

1/01/2007... 12/31/2007 $33.00

1/01/2008... 12/31/2008 $33.50

1/01/2009... 12/31/2009 $34.00

I tried working with an If statement to test the current date against the Effective and End dates, but I am not looking at it as an array.

You can look up prices with look-up option.

Define a new table for price array. Enter dates and prices for products. Define a relationship with date field.

Open options dialog box of the price field (tha table you want to get value in, not in price table).

Select Look Up value.

Select Relationship you have just defined and select price field.

Date fields can not match exactly, to solve this select "Copy Next Lower Value" or "Copy Next Upper Value" to get price value.

F. Osman Cabi