From answers to my prior questions here, I have read about known bugs with SQL and SET COLLATE. But all of the KB articles say that the problem is limited to integer fields and I'm finding that the same problem exists when all fields are numeric (10,0).

SELECT table1.perid ;
FROM table1, table2;
WHERE table1.dsid=table2.dsid and table2.dsenabled and;
table1.perid not in (select perid from table3)

I get the following count of records, depending on the collation sequence in effect at the time of the query:

DUTCH : 856
GENERAL : 856
GERMAN : 856
ICELAND : 856
MACHINE : 825
NORDAN : 856
SPANISH : 856
SWEFIN : 856
UNIQWT : 862

Again, these tables have _no_ integer fields. The right answer is 825.

Am I the last to hear about what seems to me to be a very serious bug? Does anyone know if this problem was specifically addressed in VFP7?

I need to use SET COLLATE TO "GERMAN" (among others) for my overseas users but, of course, that is really to address issues about the sorting of characters. My understanding was that SET COLLATE is only supposed to affect subsequent indexing. So after I've indexed all of my tables, can I SET COLLATE TO "MACHINE" and still rely on the sorting of character fields when I use one of the "german" indexes?

That is, can I trust code like this to produce the correct evaluation and sorting of the table1.name field?

SET COLLATE TO "MACHINE"
USE table1
INDEX ON dsid TAG dsid
SELE 2
USE table2
INDEX ON dsid TAG dsid
SET COLLATE TO "SPANISH"
INDEX ON name TAG name
SELECT table1.id
WHERE table1.id=table2.id and table2.name<"Charles";
ORDER BY table2.name

The "problem" is so intermittent that it isn't easy to test. And now my confidence is severely shaken.

Sigh. Isn't this a royal PITA for everyone developing in VFP for overseas markets (or data)? Am I missing something?

Thanks for any other info.

-Bob