Microsoft Access Error when joining a 3rd table

[Clete]

Whenever I try to join a 2nd table in a SQL query in Access I get a syntax error (missing operator) in query expression

Here is the code:

SELECT A.FileNo, A.Amount, A.Payee, A.Debtor, A.Creditor, B.TotInt
FROM CheckPayments A
INNER JOIN TotalInterest B On A.FileNo = B.FileNo
INNER JOIN File_No C On A.FileNo = C.FileNo;

 

[james_lankford]

the sql in MS Access sucks big time

you HAVE to use parenthesis for 3 table joins (at least I've never been able to make it work without them)

I can never remember how the parenthesis have to be set up
so I always create the query in the query builder just to get the basic structure
and then I change to sql view and edit the fields and formulas and whatever to get it just the way I want it

 

[Micron]

The problem is likely the spaces in these portions: CheckPayments A; TotalInterest B; File_No C
Access does not know what that is (and I don't blame it). If C is a field name, it has to be File_No.C
If not following the rules makes Access sql suck, then all program languages suck since they all have syntax rules.

 

[xenou]

Not using Access all the time now but James is write about the multiple inner joins and parens. Try:

SELECT A.FileNo, A.Amount, A.Payee, A.Debtor, A.Creditor, B.TotInt
FROM ((CheckPayments A
INNER JOIN TotalInterest B On A.FileNo = B.FileNo)
INNER JOIN File_No C On A.FileNo = C.FileNo);

If that doesn't work, build the query in design view and view the generated SQL that access creates in SQL view (edit, oh duh - exactly as suggested).

@Micron - the a, b, and c are table aliases. No problem there.

 

[Micron]

I considered that, but didn't know you could define an alias without using the AS predicate. My habit has always been to use something like CheckPayments AS A. I don't disagree with the part about parentheses, but thought the missing declaration of an alias meant they were field references. The proposed solution is one that I will often use, especially to test criteria being passed in code by getting the verbatim construct from the immediate window.

 

[james_lankford]

sucks may have been the wrong word, but neither MS SQL Server nor Oracle nor PostgreSQL nor MySQL require parenthesis

yeah, MS Access has its own syntax, but the problem is that its its own
I wish they could have developed it to follow the standard sql syntax that all databases uses

I can't tell you how many times I've done it the standard way and typed
where tbl.field like 'something%'
instead of the access way of
where tbl.field like 'something*'

then sat there scratching my head for 5 minutes trying to figure out why I'm not getting any rows back

 

[Micron]

You have my sympathy
I confess you know more about those syntaxes than me but I can relate to what you're saying, having some experience with JavaScript, PHP, html4 and cascading style sheets. Sometimes the differences are just miniscule enough to be maddening. Who would have thought the computer world would end up with so many languages to operate the same binary system?