>Hi Jill,
>
>Is the situation similar to the following? Starting on Date1, a product has a certain price; on and after Date2, the same product has a different price, etc., so you want to locate the nearest date, >= the desired date.

yes, very similar:

date and flag status

date and beneficiary status

date, amount1 and amount2

THe dates in the table have to be contiguous. You've got me started, thanks SO much, Hilmar! I'll come back if i run into trouble....J

>I have this situation; I just do SET NEAR ON, SEEK..., and SET NEAR OFF. Some additional adjustments are needed, like, if there is no exact match, go back to the previous record, and check whether you are not outside the range, i.e., you landed on another product, meaning that the desired product doesn't have a price for the desired date. Of course, I am assuming there is an index on the combination product + date.
>
>This requires some additional steps, but it is very fast.
>
>If several pieces of information are required, the UDF, instead of returning a price, can either:
>
>1. Have an additional parameter to state what field should be evaluated and returnd, or
>
>2. Especially if many fields have to be retrieved, just return the primary key, which can then easily be used in a SELECT to fetch additional fields.
>
>If this is what you need, I can search for my lookup functions, which you might then adapt.
>
>HTH,
>
>Hilmar.
>
>>Hi,
>>
>>I typically maintain history information with fields like:
>>
>>StartDate
>>EndDate
>>...values...
>>
>>When locating a record for a specific date, i can easily use Between( DateBeingSearchedFor, StartDate, EndDate ) in a LOCATE or SELECT statement.
>>
>>
>>Now i'm trying to think if i can easily just store the StartDate, i.e.,
>>
>>StartDate
>>...values...
>>
>>But i'm not coming up w/a quick and easy way to locate an entry for DateBeingSearchedFor.
>>
>>Any ideas? other than just doing a scan of the table?
>>
>>TIA, Jill