Hello,

  It is very strange, executing the same query from Access 2000 (ODBC i
think) and from Delphi 7 ADO Express, Jet 4.0 gives a complete different
result set. Access return 59 rec and Jet returns 87 rec. And the good
result set is from Access 2000!

Is there any known limitation with Jet about LEFT JOINING? Do I miss
something? I have tried to use the parametres instead with the same
result.

I have MDAC 2.7 installed and Jet 4.0 sp6 for Win 2000

Here is the query:

SELECT sd.JOB_NO, IIF(IN_OUT = "ENVOI",sd.QTY, 0) AS SENT, IIF(IN_OUT =
"RCEPTION",sd.QTY, 0) AS RECVD, IN_OUT, sd.DESCRIP, sd.PO, sd.ITEM_NO,
i.DRAWING_NO, i.REV_NO, po.SUPPLIER_NO, sp.COMP_NAME
FROM ((((INSPECTION AS i INNER JOIN SUBCONTRACT AS s ON i.JOB_NO =
s.JOB_NO) INNER JOIN SUBCONTRACT_DTL AS sd ON s.JOB_NO = sd.JOB_NO) LEFT
JOIN P_ORDER_PROM AS popr ON sd.PO = popr.PO) LEFT JOIN P_ORDER AS po ON
popr.PO = po.PO) LEFT JOIN SUPPLIER AS sp ON po.SUPPLIER_NO =
sp.SUPPLIER_NO
WHERE i.FINAL_INSPECTION  = False AND s.DESCRIP = sd.DESCRIP AND sd.QTY

ORDER BY sd.DESCRIP, sd.JOB_NO;

Thank you in advance
Serge

Hmm.  Sounds odd.

I don't really have any help for you, other than to offer that the native
Access 2000 query would have been done using DAO.  There are DAO drivers
available for Delphi:

http://www.islamov.com/diamond/overview.html

also, kadao: http://www.bcs-computer.de/kadao.htm

There also used to be  set marketed by Deersoft, not sure what's happened to
them since they were purchased by network associates last month.

I'd be curious what results you get from Delphi using a DAO component.

Regards,

Herb

news:3E42D573.DD84C777@softdelirium.qc.ca...

Serge,
  I meet the problem sometimes ago. I solve it. When I want to get the data
from the database, I create the query
component and free it after it was used for only one time! It will get the
correct result.

Owen Chan