Why can I only use INNER JOIN

[janko.klemensek]

Hello.

In the following query:

SELECT tblDokumentiDetails.ID_Artikel, - tblDokumentiDetails.Kolicina
AS Kolicina
FROM (tblDokumenti INNER JOIN tblDokumentiDetails ON
tblDokumenti.ID_Dokument = tblDokumentiDetails.ID_Dokument) INNER JOIN
[SELECT tblDokumentiDetails.ID_Artikel, Max(tblDokumenti.Datum) AS
ZadnjiDatum
FROM tblDokumenti INNER JOIN tblDokumentiDetails ON
tblDokumenti.ID_Dokument = tblDokumentiDetails.ID_Dokument
WHERE tblDokumenti.ID_Vrsta=3 And tblDokumenti.Storniran=false And
tblDokumenti.Zakljucen=true
GROUP BY tblDokumentiDetails.ID_Artikel]. AS ZadnjaInventura ON
(tblDokumentiDetails.ID_Artikel = ZadnjaInventura.ID_Artikel) AND
(ZadnjaInventura.ZadnjiDatum < tblDokumenti.Datum)
WHERE tblDokumenti.ID_Vrsta = 2 And tblDokumenti.Storniran=false And
tblDokumenti.Zakljucen=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, [e-mail] 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!