I have been working on a project for one of my customer today and came across a – well should we say – “Interesting” side od MS Access. Yes the customer has an old database in MS Access that we need to produce some reports from (in Excel).

So I did a normal SQL with some joins and got an error.

Here is the SQL i started with:

SELECT
Persons.Person_Id AS ‘Person_Id’  ,Abstracts.Abstract_Title AS ‘Abstract_Title’
,TopicsPerAbstract.Topic_Id AS ‘Topic_Id’
FROM
Abstracts
INNER JOIN Persons  ON
Abstracts.Client_Id = Persons.Client_Id
AND Abstracts.Project_Id = Persons.Project_Id
AND Abstracts.Person_Id = Persons.Person_Id
LEFT OUTER JOIN  TopicsPerAbstract  ON
Abstracts.Client_Id = TopicsPerAbstract.Client_Id
AND Abstracts.Project_Id = TopicsPerAbstract.Project_Id
AND Abstracts.Person_Id = TopicsPerAbstract.Person_Id

And I got an error back from Access – missing operand.

Well after staing on this for quite some time and trying to figure out where the error is I came across a post on the net.

Apparently MS Access need to have ( ) around JOINS to get around missing operand problem.

So the working SQL is here:

SELECT
Persons.Person_Id AS ‘Person_Id’  ,Abstracts.Abstract_Title AS ‘Abstract_Title’
,TopicsPerAbstract.Topic_Id AS ‘Topic_Id’
FROM ((
Abstracts
)
INNER JOIN Persons  ON
Abstracts.Client_Id = Persons.Client_Id
AND Abstracts.Project_Id = Persons.Project_Id
AND Abstracts.Person_Id = Persons.Person_Id
)
LEFT OUTER JOIN  TopicsPerAbstract  ON
Abstracts.Client_Id = TopicsPerAbstract.Client_Id
AND Abstracts.Project_Id = TopicsPerAbstract.Project_Id
AND Abstracts.Person_Id = TopicsPerAbstract.Person_Id

Weird