OUTER JOIN help 2

[humbletechie]

Hi,

Can someone tell me why the following SQL statement is giving this error?

Error:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Atty_2_CaseNew.Atty_ID = Tbl_Unique_Attorneys.Atty ID'.
/oe/prop65/attorneys.asp, line 48

SQL Statement:
"SELECT Atty_2_CaseNew.Firm , Tbl_Unique_Attorneys.Attorney FROM Atty_2_CaseNew LEFT OUTER JOIN Tbl_Unique_Attorneys ON Atty_2_CaseNew.Atty_ID = Tbl_Unique_Attorneys.Atty ID WHERE Tbl_Unique_Attorneys.Atty ID Is Not Null"

Thanks in advance!

 

[GaryC123]

"SELECT Atty_2_CaseNew.Firm , Tbl_Unique_Attorneys.Attorney FROM Atty_2_CaseNew LEFT OUTER JOIN Tbl_Unique_Attorneys ON Atty_2_CaseNew.Atty_ID = Tbl_Unique_Attorneys.Atty_ID WHERE Tbl_Unique_Attorneys.Atty_ID Is Not Null"

The underscores maybe?

 

[humbletechie]

I tried taking out the underscores from the code and the tables and I am still getting the syntax error. Below is more code:

<%
Dim objConn
Dim connStr
Dim Firms

Set objConn = Server.CreateObject(&quot;ADODB.Connection&quot
connStr = &quot;DSN=moosensquirrel;uid=;pwd=&quot;
objConn.Open(connStr)
CONST adOpenStatic = 3
CONST adLockOptimistic = 3

set Firms = Server.CreateObject(&quot;ADODB.Recordset&quot
Firms.ActiveConnection = objConn
Firms.Source = &quot;SELECT Atty2CaseNew.Firm , TblUniqueAttorneys.Attorney FROM Atty2CaseNew LEFT OUTER JOIN TblUniqueAttorneys ON Atty2CaseNew.AttyID = TblUniqueAttorneys.Atty ID WHERE TblUniqueAttorneys.Atty ID Is Not Null&quot;
Firms.Open()

While Not Firms.EOF
%>

 

[yogi77]

I think GaryC123 was suggesting that you need to include the underscores for the field Atty_ID or is the field called AttyID? Either way, it won't like Atty ID with a space, unless you use the square bracket notation [Atty ID]

SELECT Atty2CaseNew.Firm , TblUniqueAttorneys.Attorney FROM Atty2CaseNew LEFT OUTER JOIN TblUniqueAttorneys ON Atty2CaseNew.AttyID = TblUniqueAttorneys.Atty_ID WHERE TblUniqueAttorneys.Atty_ID Is Not Null

 

[humbletechie]

Yep, that was it. Thank you VERY much to the both of you for your help.