>The only reason is that FP 2.5 table is external for VFP application and its structure can't be modified.

From e-mail:
>Here are table AD as Table1 and CAN as Table2.
>I try to do

>SELECT Can.Res FROM Ad, Can WHERE Ad.Nom = Can.Nom AND Ad.Fam = "A"
>OR
>SELECT Can.Res FROM Ad, Can WHERE Ad.Nom = Can.Nom AND Ad.Nom = CHR(18)+CHR(176)


Hi Vassili,

I looked at it and it does not look good. :)

If we look at the records in both tables, we have the following DOS ASCII codes in Nom field (Id field)

*Table1.Nom
CHR(18)+CHR(176) 
CHR(18)+CHR(178)

*Table2.Nom
CHR(18)+CHR(176) 
CHR(18)+CHR(178)
CHR(18)+CHR(176)

So, the query like

SELECT Can.Res FROM Ad, Can WHERE Ad.Nom = Can.Nom AND Ad.Fam = "A"

brings 2 records in FP2.5 DOS. However, under Windows 1252 (that I have) the records look like:

*Table1.Nom
CHR(18)+CHR(32) 
CHR(18)+CHR(32)

*Table2.Nom
CHR(18)+CHR(32) 
CHR(18)+CHR(32)
CHR(18)+CHR(32)

The most I could get from SQL was:

SELECT Can.Res FROM Can, ad WHERE CPCONVERT(866,1252,Ad.Nom) = CPCONVERT(866,1252,Can.Nom) AND Ad.Fam = "A"

* brings 3 records under VFP, while under DOS

SELECT Can.Res FROM Ad, Can WHERE Ad.Nom = Can.Nom AND Ad.Fam = "A"

correctly brings 2 records.

Using CPCONVERT() function in SQL generally would work, however the trouble is that in VFP conversion table used internally, both CHR(176) and CHR(178) characters you used in the key field, get converted to CHR(32). So VFP then cannot distinguish the records in SQL SELECT. This also happens with other letters too. You can see the full list of disrepancies, if you run the following code:

FOR n = 128 TO 255
? n, CHR(n),  CPCONVERT(866,1251,CHR(n)), ASC(CPCONVERT(866,1251,CHR(n)))
ENDFOR

However, if you run VFP with CONFIG.FPW line

CODEPAGE=866

your original statement

SELECT Can.Res FROM Ad, Can WHERE Ad.Nom = Can.Nom AND Ad.Fam = "A"

will work properly. Displaying the correct characters in the result is another story. :)

I am not sure how you will work around all this mess. :) The ID field should not use the binary characters in the first place.