I keep getting a sytnax error in my from clause.  I have written the 
string every way i know how and can't seem to get past the error.  i have 
use AS command to name the aliase, but it doesn't seem to matter. Any 
suggestions would be greatly appreciated

here is my code:

<%@ Language="VBScript" %>

<% 'option explicit %>

<%
response.buffer = true

Dim i
Dim MediaID
Dim cmdTemp
Dim MediaInfo
Dim ICS
Dim sqlstr
Dim responseredir
Dim Result

    Set ICS = Server.CreateObject("ADODB.Connection")
    ICS.ConnectionTimeout = Application("leg_ConnectionTimeout")
    ICS.CommandTimeout = Application("leg_CommandTimeout")

    'Response.Write Application("leg_ConnectionString")
    ICS.Open Application("leg_ConnectionString"), Application
("leg_RuntimeUserName"), Application("leg_RuntimePassword")

    Set cmdTemp = Server.CreateObject("ADODB.Command")

    Set MediaInfo = Server.CreateObject("ADODB.Recordset")
   

    sqlstr = "SELECT * " & _
		"FROM index i " & _
		"INNER JOIN legislation l ON " & _
			"i.FirstName = l.SponsorFirstName AND i.LastName = 
l.SponsorLastName " & _
		"INNER JOIN profiles p ON " & _
			"i.FirstName = p.FirstName AND i.LastName = 
p.LastName"	
			
	If Request.Form("housename") <> "" Then
			sqlstr = sqlstr & "Where i.ID = '" & Request.Form
("housename") & "'"
			End if
	If Request.Form("senatename") <> "" Then
			sqlstr = sqlstr & "Where i.ID = '" & Request.Form
("senatename") & "'"
			End If
	
	'sqlstr = sqlstr & " Order by State ASC"
    
   
   response.write "Sql String: " & sqlstr
    
    cmdTemp.CommandText = sqlstr
    cmdTemp.CommandType = 1
    
   'response.write "Sql String: " & cmdtemp.commandtext

    Set cmdTemp.ActiveConnection = ICS
    MediaInfo.Open cmdTemp, , 1, 3
        

%>

Here is the error message:

Sql String: SELECT * FROM index i INNER JOIN legislation l ON i.FirstName 
= l.SponsorFirstName AND i.LastName = l.SponsorLastName INNER JOIN 
profiles p ON i.FirstName = p.FirstName AND i.LastName = p.LastName 
Microsoft OLE DB Provider for ODBC Drivers error '80040e14' 

[Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause. 

/er-dev/leg/profiles.asp, line 58 

Thanks for all your help,
andy

In Access, select "Queries", "New in Designer", and then hit the little SQL
button once you've built the query. It'll show you the SQL you need to use.

The main problem is that in Access you need to JOIN onto a table, or a
table-entity (I think that's what they are called), so you can't do:

FROM TableX
INNER JOIN TableY
ON TableX.PK = TableY.FK
INNER JOIN TableZ
ON TableY.PK = TableZ.FK

instead you need to do:

FROM
    TableX
INNER JOIN
    (
        TableY
    INNER JOIN
        TableZ
    ON
        TableY.PK = TableZ.FK
    )
ON
    TableX.PK = TableY.FK

(ie one join needs to be done inside the brackets, and then you JOIN onto
this in your second join).

Also, you do need the AS clause to alias as table.

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "andy qualls" <andy_qualls@h...>
Subject: [access_asp] Syntax error in From clause with INNER JOIN


: I keep getting a sytnax error in my from clause.  I have written the
: string every way i know how and can't seem to get past the error.  i have
: use AS command to name the aliase, but it doesn't seem to matter. Any
: suggestions would be greatly appreciated
:
: here is my code:
:
: <%@ Language="VBScript" %>
:
: <% 'option explicit %>
:
: <%
: response.buffer = true
:
: Dim i
: Dim MediaID
: Dim cmdTemp
: Dim MediaInfo
: Dim ICS
: Dim sqlstr
: Dim responseredir
: Dim Result
:
:     Set ICS = Server.CreateObject("ADODB.Connection")
:     ICS.ConnectionTimeout = Application("leg_ConnectionTimeout")
:     ICS.CommandTimeout = Application("leg_CommandTimeout")
:
:     'Response.Write Application("leg_ConnectionString")
:     ICS.Open Application("leg_ConnectionString"), Application
: ("leg_RuntimeUserName"), Application("leg_RuntimePassword")
:
:     Set cmdTemp = Server.CreateObject("ADODB.Command")
:
:     Set MediaInfo = Server.CreateObject("ADODB.Recordset")
:
:
:     sqlstr = "SELECT * " & _
: "FROM index i " & _
: "INNER JOIN legislation l ON " & _
: "i.FirstName = l.SponsorFirstName AND i.LastName 
: l.SponsorLastName " & _
: "INNER JOIN profiles p ON " & _
: "i.FirstName = p.FirstName AND i.LastName 
: p.LastName"
:
: Sql String: SELECT * FROM index i INNER JOIN legislation l ON i.FirstName
: = l.SponsorFirstName AND i.LastName = l.SponsorLastName INNER JOIN
: profiles p ON i.FirstName = p.FirstName AND i.LastName = p.LastName
: Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
:
: [Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.
:
: /er-dev/leg/profiles.asp, line 58