Prev: treeview question
Next: Help with Query Please
From: Neil Gould on 9 Jul 2010 12:04
So simple, yet so puzzling. Searches have turned up nothing useful, so I
hope someone here can provide some insights.

This SQL statement:

cJEnq = "SELECT Mem_ID, Model, Year FROM Cars WHERE Cars.Make = 'MG' AND
Cars.Model = 'B' INNER JOIN Members ON Cars.Mem_ID = Members.Mem_ID"

Results in this error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in
query expression 'Cars.Make = 'MG' AND Cars.Model = 'B' INNER JOIN Members
ON Cars.Mem_ID = Members.Mem_ID'.

What is the missing operator or error that leads to the notion that there is
one (if the INNER JOIN clause is excluded, the query works as expected)?

--
TIA

Neil





From: Douglas J. Steele on 9 Jul 2010 12:55
The WHERE clause goes after the JOIN

cJEnq = "SELECT Mem_ID, Model, Year " & _
"FROM Cars INNER JOIN Members " & _
"ON Cars.Mem_ID = Members.Mem_ID "& _
"WHERE Cars.Make = 'MG' AND Cars.Model = 'B'"

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
Co-author: Access 2010 Solutions, published by Wiley
(no e-mails, please!)

"Neil Gould" <neil(a)myplaceofwork.com> wrote in message
news:i17h76$mms$1(a)speranza.aioe.org...
> So simple, yet so puzzling. Searches have turned up nothing useful, so I
> hope someone here can provide some insights.
>
> This SQL statement:
>
> cJEnq = "SELECT Mem_ID, Model, Year FROM Cars WHERE Cars.Make = 'MG' AND
> Cars.Model = 'B' INNER JOIN Members ON Cars.Mem_ID = Members.Mem_ID"
>
> Results in this error:
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
>
> [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator)
> in
> query expression 'Cars.Make = 'MG' AND Cars.Model = 'B' INNER JOIN Members
> ON Cars.Mem_ID = Members.Mem_ID'.
>
> What is the missing operator or error that leads to the notion that there
> is
> one (if the INNER JOIN clause is excluded, the query works as expected)?
>
> --
> TIA
>
> Neil
>
>
>
>
>


From: Bob Barrows on 9 Jul 2010 12:57
Neil Gould wrote:
> So simple, yet so puzzling. Searches have turned up nothing useful,
> so I hope someone here can provide some insights.
>
> This SQL statement:
>
> cJEnq = "SELECT Mem_ID, Model, Year FROM Cars WHERE Cars.Make = 'MG'
> AND Cars.Model = 'B' INNER JOIN Members ON Cars.Mem_ID =
> Members.Mem_ID"
>
> Results in this error:
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
>
> [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
> operator) in query expression 'Cars.Make = 'MG' AND Cars.Model = 'B'
> INNER JOIN Members ON Cars.Mem_ID = Members.Mem_ID'.
>
> What is the missing operator or error that leads to the notion that
> there is one (if the INNER JOIN clause is excluded, the query works
> as expected)?

Errr .... you cannot have a WHERE clause before a join statement ...
The join is part of the FROM clause, and the WHERE clause has to come
after the FROM clause.

cJEnq = "SELECT Mem_ID, Model, Year FROM Cars " & _
" INNER JOIN Members ON Cars.Mem_ID =Members.Mem_ID" & _
" WHERE Cars.Make = 'MG' AND Cars.Model = 'B'"


--
HTH,
Bob Barrows


From: Neil Gould on 9 Jul 2010 13:33
Bob Barrows wrote:
> Errr .... you cannot have a WHERE clause before a join statement ...
> The join is part of the FROM clause, and the WHERE clause has to come
> after the FROM clause.
>
> cJEnq = "SELECT Mem_ID, Model, Year FROM Cars " & _
> " INNER JOIN Members ON Cars.Mem_ID =Members.Mem_ID" & _
> " WHERE Cars.Make = 'MG' AND Cars.Model = 'B'"
>
Thanks, Bob!

--
Neil





From: Neil Gould on 9 Jul 2010 13:33
Douglas J. Steele wrote:
> The WHERE clause goes after the JOIN
>
> cJEnq = "SELECT Mem_ID, Model, Year " & _
> "FROM Cars INNER JOIN Members " & _
> "ON Cars.Mem_ID = Members.Mem_ID "& _
> "WHERE Cars.Make = 'MG' AND Cars.Model = 'B'"
>
Thanks, Douglas!

--
Neil


 | 
Pages: 1
Prev: treeview question
Next: Help with Query Please