Why can I only use INNER JOIN

[janko.klemensek]

Hello.

In the following query:

SELECT tblDokumentiDet ails.ID_Artikel , - tblDokumentiDet ails.Kolicina
AS Kolicina
FROM (tblDokumenti INNER JOIN tblDokumentiDet ails ON
tblDokumenti.ID _Dokument = tblDokumentiDet ails.ID_Dokumen t) INNER JOIN
[SELECT tblDokumentiDet ails.ID_Artikel , Max(tblDokument i.Datum) AS
ZadnjiDatum
FROM tblDokumenti INNER JOIN tblDokumentiDet ails ON
tblDokumenti.ID _Dokument = tblDokumentiDet ails.ID_Dokumen t
WHERE tblDokumenti.ID _Vrsta=3 And tblDokumenti.St orniran=false And
tblDokumenti.Za kljucen=true
GROUP BY tblDokumentiDet ails.ID_Artikel]. AS ZadnjaInventura ON
(tblDokumentiDe tails.ID_Artike l = ZadnjaInventura .ID_Artikel) AND
(ZadnjaInventur a.ZadnjiDatum < tblDokumenti.Da tum)
WHERE tblDokumenti.ID _Vrsta = 2 And tblDokumenti.St orniran=false And
tblDokumenti.Za kljucen=true;
I want to use LEFT JOIN instead of INNER JOIN at second join (INNER
JOIN [SELECT...), but I get error: Join expression not supported. Can
you help me please? Must I put parentheses some way there? What is the
use of "[" and "]."?

[BillCo]

What is the

use of "[" and "]."?

The [ ]. denotes a sub-query. in this case you are joining to a sub-
query named ZadnjaInventura .

The access treatment of SQL code behind sub-queries is a little odd.
When editing a query with a sub-query in the SQL editor, you need to
replace the "[...]. AS ..." with "(...) AS ...", otherwise the jet
compiler won't be able to handle it. Once you edit in the graphic
editor, it converts it back to the "[...]. AS ..." format.

So try either:
1. edit the whole thing in the graphic editor - double click the line
representing the join and select from the options to create a left
join, or
2. edit in the SQL editor, but always replace "[" with "(" and "]."
with ")" before you run it

Good luck

[janko.klemensek]

graphic editor complains something... when I replace "[" and "]." with
"(", ")" it stil works. But when I change INNER to LEFT (JOIN) same
error. grrr.

BillCo je napisal:

What is the
use of "[" and "]."?

The [ ]. denotes a sub-query. in this case you are joining to a sub-
query named ZadnjaInventura .

The access treatment of SQL code behind sub-queries is a little odd.
When editing a query with a sub-query in the SQL editor, you need to
replace the "[...]. AS ..." with "(...) AS ...", otherwise the jet
compiler won't be able to handle it. Once you edit in the graphic
editor, it converts it back to the "[...]. AS ..." format.

So try either:
1. edit the whole thing in the graphic editor - double click the line
representing the join and select from the options to create a left
join, or
2. edit in the SQL editor, but always replace "[" with "(" and "]."
with ")" before you run it

Good luck

[BillCo]

On May 21, 8:50 pm, janko.klemen... @gmail.com wrote:

graphic editor complains something... when I replace "[" and "]." with
"(", ")" it stil works. But when I change INNER to LEFT (JOIN) same
error. grrr.

that's odd - check for data corruption in the tables and make sure the
the joined fields have the exact same data type attributes. beyond
that i'm mystified as you!