Finding a value in effect on a given date withing a date indexed field

[JBDBF]

I am working in Dbase and need a solution for finding a record in a database that is indexed on a date field within the database. The database tracks changes to the valuation of a given piece of equipment. Changes to the valuation may or may not occur on a consistent basis. I need to search the database and a return the corresponding valuation that was in effect on that date. The date may or may not be an exact match and the index is on the dates of that the value changed. What code can I use to simplify finding the value that was in existence on a day that there isn't a corresponding match to a date within the database as the FIND command will not find the record? All help is appreciated, thanks

 

[dbMark]

I haven't used FIND for a long time and so I'm not sure of the syntax, I used SEEK() instead with dBase 5 for DOS. Since it wouldn't work with Windows versions after XP I converted code and upgraded completely to Visual FoxPro 9.

During the SEEK only, would SET NEAR ON work? You might also need SET EXACT OFF in case you have more than the one field in the index or, for example, in case you want to seek by only part of the date such as by year+month or year only. Also experiment with DESCENDING order in case that helps, indexes default to ASCENDING order. Between those three commands you should find a way for it to work.

One final thought. When building evaluated indexes combining multiple items you can use DTOS(DateField) since VFP requires all data types converted to character types in the index expression.