>I always use SET NEAR ON and SET EXECT OFF so I don't know why
>this has bit me in the rear now. Thanks all for your help!


Norm, just to be clear, my experience was that there really was something wrong with my table which went away when I reappended all the records.

As I remember if I compared equality to the datetime value in a single record it would compare true but if I issued a count on the equality it would fail, presumably because of Rushmore optimization.

So I think the index was wrong but REINDEX would not fix it.


I am skeptical of Erik's explanation that the table values contain milliseconds. I wrote this test program:

local	lnI, ltCheck
local	lnCount1, lnCount2

use DT exclusive
**  DT contains one field:  tTest  datetime   8

set safety off
zap
set safety on
clear

for lnI = 1 to 200
append blank
replace tTest with datetime()
endfor

count to lnCount1
? lnCount1, " records in DT"

go top
ltCheck = DT.tTest
count for DT.tTest = ltCheck to lnCount1
? lnCount1

go bottom
ltCheck = DT.tTest
count for DT.tTest = ltCheck to lnCount2
? lnCount2
? lnCount1 + lnCount2

I've run the program about ten times and each time I would see numbers like this:
200
13
187
200

The first and last lines displayed were always 200 with one exception. This means that a second boundary is being crossed during the append operation. In the one exception I saw:
200
200
200
400

which meant that all of the records were written within the same second.

Clearly not all of the records are being written within the same millisecond so if the datetime field contained milliseconds most of the records would not be equal to the first or last record.

Peter