Hi,
Delphi 5, MSAccess2000, MS Jet 4.0

I'm trying to execute the following query using a TADOQuery:

        Add(format('SELECT l.%s, ',[fldLoadID]));
        Add(format('l.%s, l.%s, ',[fldOrigLocationID, fldDestLocationID]));
        Add(format('SUM(lsm.%s) AS %s',[fldActivityID, fldExpr1]));
        Add(format('FROM %s l',[tblLoads]));
        Add(format('LEFT OUTER JOIN %s lsm',[tblLoadStopMoves]));
        Add(format('ON l.%s = lsm.%s',[fldLoadID, fldLoadID]));
        Add(format('AND l.%s = ''A''',[fldActiveStatus]));
        Add(format('GROUP BY l.%s, l.%s, ',[fldLoadID, fldOrigLocationID]));
        Add(format('l.%s, l.%s',[fldDestLocationID, fldActiveStatus]));
        Add(format('ORDER BY l.%s',[fldLoadID]));

When debugging I get to the third line (the one with the SUM in it) and I
get an access violation in msjet40.dll and then an oleexception saying "join
expression not supported".  Any ideas?  I have no problem with this when I
use a SQLServer2000.  Do I need a different version of Jet? Or???

Thanks in advance for your help!!

--
John Kring
jkr...@new.rr.com

I've found that Access is a bit funny with it's join syntax.  Try creating
the query in Access, and look at the SQL it produces.  I think the brackets
are important for Access when trying to use joins.  When writing SQL for
Access I write the Query in Access using their 'Design Query' functionality.
Then I view the finished query as SQL, and cut and paste it into Delphi.
The SQL syntax Access produces is different than the standard I believe.

You can set the desired SQL language standard as a property in the
command object.

--
Arthur Hoornweg

(If you found this message in a Usenet newsgroup, you
should remove the ".net" from my e-mail address)