Hi All

I have a table containing several million client records. There are multiple records per client. Each client has a unique 8 character code. Each record also contains a date and a numeric field, amongst others.

I can easily sort the table by Client_Code + date by creating an index on Client_Code + Dtos(Date_Field). My problem is I also need to sort the records by the numeric field within client code. i.e. Client_code + Numeric_Field.

I tried Client_Code + Str( Numeric_Field, 10 ) which works as long as the Numeric_Field contains positive numbers. When the Numeric_Field contains negative numbers then the Str() function will not sort the field correctly by numeric value but, obviously, by the ASCII value of the resulting Str() function .... negative numbers appear after the largest positive numbers because of the '-' sign.

It is not just sorting that I need but the ability to Seek() to the closest matching numeric value within a specific Client_Code (nag, nag, nag). e.g. in pseudo code:

Set Near On
=Seek( Client_Code + Numeric_Field )
Set Near Off

I realize that I could read through all client records or Select all client records and then index and seek the resulting subset of records. However, there can be several thousand client records and it grows daily. Plus I need to create a report of closest matching records (by numeric field) for all clients everyday!

Any ideas? Thanks.