Tamar,

Since it works in the Query Browser and not through the CA, seems to me the problem is somehow in how you're setting things up for the CA. Can you show us that code?

Basically, this is what is happening in my CA class. It has the ff properties: cSelect, cFrom, cOrder, cWhere.

My form class then has several controls which alter the contents of the above properties. For instance, I have a textbox where user types in some value. There is a combobox that allows user to choose field/column to search. Once user hits the Search button, it constructs the CA's SelectCmd on the fly.

To check all 'constructed' SelectCmds, I put a MESSAGEBOX trap in the BeforeCursorFill() event. All SELECT statements shown are 'correct' in the sense that if I copy and paste the same in MySQL Query Browser, it works.

SELECT A.REFNO, A.DATE, A.SUPPLIERID, A.TERMS, A.TOTQTY, A.AMOUNT, A.REMARKS,
     A.PREPAREDBY, A.CHECKEDBY, A.PRINTDATE, A.POSTDATE, A.CLOSED, A.CANCELLED, A.ID,
     B.CODE AS SUPPCODE, B.DESCRIPTOR AS SUPPNAME, B.ADDRESS AS SUPPADDR
FROM pohdr A JOIN SUPPLIER B ON A.SUPPLIERID=B.ID  WHERE B.DESCRIPTOR LIKE 'ABC%'
ORDER BY REFNO

As you can see from the above code, B.DESCRIPTOR has an alias of SUPPNAME. If I use:

WHERE SUPPNAME LIKE 'ABC%'

It generates an error both on the VFP side, and on MySQL Query Browser.

If I change it to:

WHERE B.DESCRIPTOR LIKE 'ABC%'

It works on MySQL Query Browser. In VFP side though, it first shows an error (trapped by my code) first (Unknown column in where clause), but shows the correct results.

So far, this is what I have found out (and perhaps may be a good clue):
The error comes up ONLY WHEN THE FIELD IN THE WHERE CLAUSE IS FROM THE FOREIGN TABLE (Supplier). It works if the field 'belongs' to the first table.

I have also tried the ff (all with same results):

- using MySQL ODBC 5.1 instead 3.51.27
- using the complete table names as field prefixes

Dennis