Jet finds a syntax error in my SQL statement

P

Pato-chan

I'm trying to alter a report's recordsource using VB code, but the Jet engine
doesn't like my SQL. It is adding brackets where they don't belong. I tried
replacing the brackets with parentheses, but it didn't like that either. Is
there anywhere to go online to figure out what Jet thinks of as proper
syntax? (I'm using Access03 SP3.)

From VB Code: SQLquery =
SELECT Q.SS, Q.AssignmentEndDate, Employees.QuitReason,
Employees_1.[Employee Name] FROM (Employees RIGHT JOIN [SELECT A.Post AS
Post, A.AssignmentEndDate, A.AssignedEmployeeSS AS SS FROM Assignments AS A].
AS Q ON (Employees.EndDate = Q.AssignmentEndDate) AND (Employees.SS = Q.SS))
INNER JOIN Employees AS Employees_1 ON Q.SS = Employees_1.SS WHERE
(((Q.Post)= '" & cmbPost.Value & "') AND ((Q.AssignmentEndDate) Is Not Null))

From the report's recordsource
SELECT Q.SS, Q.AssignmentEndDate, Employees.QuitReason,
Employees_1.[Employee Name] FROM (Employees RIGHT JOIN [SELECT A].[Post AS
Post, A].[AssignmentEndDate, A].[AssignedEmployeeSS AS SS FROM Assignments AS
A; ] AS Q ON (Employees.SS=Q.SS) AND (Employees.EndDate=Q.AssignmentEndDate))
INNER JOIN Employees AS Employees_1 ON Q.SS=Employees_1.SS WHERE
(((Q.Post)='Envelope Repair') And ((Q.AssignmentEndDate) Is Not Null));


My SQL code inside the VB code looks as follows:

SQLquery = "SELECT Q.SS, Q.AssignmentEndDate, Employees.QuitReason,
Employees_1.[Employee Name] FROM (Employees RIGHT JOIN [SELECT A.Post AS
Post, A.AssignmentEndDate, A.AssignedEmployeeSS AS SS FROM Assignments AS
A;]. AS Q ON (Employees.EndDate = Q.AssignmentEndDate) AND (Employees.SS =
Q.SS)) INNER JOIN Employees AS Employees_1 ON Q.SS = Employees_1.SS WHERE
(((Q.Post)= '" & cmbPost.Value & "') AND ((Q.AssignmentEndDate) Is Not Null))"
Reports(stDocName).RecordSource = SQLquery

In an earlier post John Vinson suggested I save the "SELECT A.Post AS Post,
A.AssignmentEndDate, A.AssignedEmployeeSS AS SS FROM Assignments AS A"
portion as a named query and join it, rather than trying to do it as a
subquery. I'm not exactly sure what that means, but assume it entails using
the CreateQueryDef method. I can do that, but am unsure how to embed this
querydef into another text string that would call it.....
 
M

Michael Gramelspacher

My SQL code inside the VB code looks as follows:

SQLquery = "SELECT Q.SS, Q.AssignmentEndDate, Employees.QuitReason,
Employees_1.[Employee Name] FROM (Employees RIGHT JOIN [SELECT A.Post AS
Post, A.AssignmentEndDate, A.AssignedEmployeeSS AS SS FROM Assignments AS
A;]. AS Q ON (Employees.EndDate = Q.AssignmentEndDate) AND (Employees.SS =
Q.SS)) INNER JOIN Employees AS Employees_1 ON Q.SS = Employees_1.SS WHERE
(((Q.Post)= '" & cmbPost.Value & "') AND ((Q.AssignmentEndDate) Is Not Null))"
Reports(stDocName).RecordSource = SQLquery
Click to expand...

Try it like this:

SQLquery = "SELECT Q.SS, Q.AssignmentEndDate, Employees.QuitReason,
Employees_1.[Employee Name] FROM (Employees RIGHT JOIN (SELECT A.Post AS
Post, A.AssignmentEndDate, A.AssignedEmployeeSS AS SS FROM Assignments AS
A) AS Q ON (Employees.EndDate = Q.AssignmentEndDate) AND (Employees.SS =
Q.SS)) INNER JOIN Employees AS Employees_1 ON Q.SS = Employees_1.SS WHERE
(((Q.Post)= '" & cmbPost.Value & "') AND ((Q.AssignmentEndDate) Is Not Null))"
Reports(stDocName).RecordSource = SQLquery
 
M

Michael Gramelspacher

My SQL code inside the VB code looks as follows:

SQLquery = "SELECT Q.SS, Q.AssignmentEndDate, Employees.QuitReason,
Employees_1.[Employee Name] FROM (Employees RIGHT JOIN [SELECT A.Post AS
Post, A.AssignmentEndDate, A.AssignedEmployeeSS AS SS FROM Assignments AS
A;]. AS Q ON (Employees.EndDate = Q.AssignmentEndDate) AND (Employees.SS =
Q.SS)) INNER JOIN Employees AS Employees_1 ON Q.SS = Employees_1.SS WHERE
(((Q.Post)= '" & cmbPost.Value & "') AND ((Q.AssignmentEndDate) Is Not Null))"
Reports(stDocName).RecordSource = SQLquery
Click to expand...
Click to expand...

Oops! try this.

SQLquery ="SELECT Q.SS, Q.AssignmentEndDate, Employees.QuitReason, " & _
"Employees_1.[Employee Name] FROM (Employees RIGHT JOIN (SELECT A.Post AS " & _
"Post, A.AssignmentEndDate, A.AssignedEmployeeSS AS SS FROM Assignments AS " & _
"A) AS Q ON (Employees.EndDate = Q.AssignmentEndDate) AND (Employees.SS = " & _
"Q.SS)) INNER JOIN Employees AS Employees_1 ON Q.SS = Employees_1.SS WHERE " & _
"(((Q.Post)= '" & cmbPost.Value & "') AND ((Q.AssignmentEndDate) Is Not Null))"
Reports(stDocName).RecordSource = SQLquery
 
P

Pato-chan

Then I get a syntax error in the FROM clause because the jet engine changed
the recordsource to be as follows:

SELECT Q.SS, Q.AssignmentEndDate, Employees.QuitReason,
Employees_1.[Employee Name] FROM (Employees RIGHT JOIN [SELECT A].[Post AS
Post, A].[AssignmentEndDate, A].[AssignedEmployeeSS AS SS FROM Assignments AS
A; ] AS Q ON (Employees.SS=Q.SS) AND (Employees.EndDate=Q.AssignmentEndDate))
INNER JOIN Employees AS Employees_1 ON Q.SS=Employees_1.SS WHERE
(((Q.Post)='Envelope Repair') And ((Q.AssignmentEndDate) Is Not Null));

Michael Gramelspacher said:
My SQL code inside the VB code looks as follows:

SQLquery = "SELECT Q.SS, Q.AssignmentEndDate, Employees.QuitReason,
Employees_1.[Employee Name] FROM (Employees RIGHT JOIN [SELECT A.Post AS
Post, A.AssignmentEndDate, A.AssignedEmployeeSS AS SS FROM Assignments AS
A;]. AS Q ON (Employees.EndDate = Q.AssignmentEndDate) AND (Employees.SS =
Q.SS)) INNER JOIN Employees AS Employees_1 ON Q.SS = Employees_1.SS WHERE
(((Q.Post)= '" & cmbPost.Value & "') AND ((Q.AssignmentEndDate) Is Not Null))"
Reports(stDocName).RecordSource = SQLquery
Click to expand...

Try it like this:

SQLquery = "SELECT Q.SS, Q.AssignmentEndDate, Employees.QuitReason,
Employees_1.[Employee Name] FROM (Employees RIGHT JOIN (SELECT A.Post AS
Post, A.AssignmentEndDate, A.AssignedEmployeeSS AS SS FROM Assignments AS
A) AS Q ON (Employees.EndDate = Q.AssignmentEndDate) AND (Employees.SS =
Q.SS)) INNER JOIN Employees AS Employees_1 ON Q.SS = Employees_1.SS WHERE
(((Q.Post)= '" & cmbPost.Value & "') AND ((Q.AssignmentEndDate) Is Not Null))"
Reports(stDocName).RecordSource = SQLquery
Click to expand...
 
J

John Spencer

I would think that the following SQL would work

STRSQL = "SELECT Q.SS, Q.AssignmentEndDate, Employees.QuitReason" & _
", Employees_1.[Employee Name] " & _
" FROM (Employees RIGHT JOIN " & _
" (SELECT A.Post AS Post, A.AssignmentEndDate" & _
", A.AssignedEmployeeSS AS SS " & _
" FROM Assignments AS A) AS Q " & _
" ON Employees.SS=Q.SS AND Employees.EndDate=Q.AssignmentEndDate)" & _
" INNER JOIN Employees AS Employees_1 ON Q.SS=Employees_1.SS " & _
" WHERE Q.Post='Envelope Repair' And Q.AssignmentEndDate) Is Not Null"


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Pato-chan said:
Then I get a syntax error in the FROM clause because the jet engine
changed
the recordsource to be as follows:

SELECT Q.SS, Q.AssignmentEndDate, Employees.QuitReason,
Employees_1.[Employee Name] FROM (Employees RIGHT JOIN [SELECT A].[Post AS
Post, A].[AssignmentEndDate, A].[AssignedEmployeeSS AS SS FROM Assignments
AS
A; ] AS Q ON (Employees.SS=Q.SS) AND
(Employees.EndDate=Q.AssignmentEndDate))
INNER JOIN Employees AS Employees_1 ON Q.SS=Employees_1.SS WHERE
(((Q.Post)='Envelope Repair') And ((Q.AssignmentEndDate) Is Not Null));

Michael Gramelspacher said:
My SQL code inside the VB code looks as follows:

SQLquery = "SELECT Q.SS, Q.AssignmentEndDate, Employees.QuitReason,
Employees_1.[Employee Name] FROM (Employees RIGHT JOIN [SELECT A.Post AS
Post, A.AssignmentEndDate, A.AssignedEmployeeSS AS SS FROM Assignments
AS
A;]. AS Q ON (Employees.EndDate = Q.AssignmentEndDate) AND (Employees.SS
=
Q.SS)) INNER JOIN Employees AS Employees_1 ON Q.SS = Employees_1.SS
WHERE
(((Q.Post)= '" & cmbPost.Value & "') AND ((Q.AssignmentEndDate) Is Not
Null))"
Reports(stDocName).RecordSource = SQLquery
Click to expand...

Try it like this:

SQLquery = "SELECT Q.SS, Q.AssignmentEndDate, Employees.QuitReason,
Employees_1.[Employee Name] FROM (Employees RIGHT JOIN (SELECT A.Post AS
Post, A.AssignmentEndDate, A.AssignedEmployeeSS AS SS FROM Assignments AS
A) AS Q ON (Employees.EndDate = Q.AssignmentEndDate) AND (Employees.SS =
Q.SS)) INNER JOIN Employees AS Employees_1 ON Q.SS = Employees_1.SS WHERE
(((Q.Post)= '" & cmbPost.Value & "') AND ((Q.AssignmentEndDate) Is Not
Null))"
Reports(stDocName).RecordSource = SQLquery
Click to expand...
Click to expand...
 
P

Pato-chan

At least I get a different error message with this. It now says I have an
extra parenthesis and highlights the last closing parenthesis before the Is
Not Null statement.

John Spencer said:
I would think that the following SQL would work

STRSQL = "SELECT Q.SS, Q.AssignmentEndDate, Employees.QuitReason" & _
", Employees_1.[Employee Name] " & _
" FROM (Employees RIGHT JOIN " & _
" (SELECT A.Post AS Post, A.AssignmentEndDate" & _
", A.AssignedEmployeeSS AS SS " & _
" FROM Assignments AS A) AS Q " & _
" ON Employees.SS=Q.SS AND Employees.EndDate=Q.AssignmentEndDate)" & _
" INNER JOIN Employees AS Employees_1 ON Q.SS=Employees_1.SS " & _
" WHERE Q.Post='Envelope Repair' And Q.AssignmentEndDate) Is Not Null"


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Pato-chan said:
Then I get a syntax error in the FROM clause because the jet engine
changed
the recordsource to be as follows:

SELECT Q.SS, Q.AssignmentEndDate, Employees.QuitReason,
Employees_1.[Employee Name] FROM (Employees RIGHT JOIN [SELECT A].[Post AS
Post, A].[AssignmentEndDate, A].[AssignedEmployeeSS AS SS FROM Assignments
AS
A; ] AS Q ON (Employees.SS=Q.SS) AND
(Employees.EndDate=Q.AssignmentEndDate))
INNER JOIN Employees AS Employees_1 ON Q.SS=Employees_1.SS WHERE
(((Q.Post)='Envelope Repair') And ((Q.AssignmentEndDate) Is Not Null));

Michael Gramelspacher said:
On Wed, 9 Jan 2008 09:18:02 -0800, Pato-chan

My SQL code inside the VB code looks as follows:

SQLquery = "SELECT Q.SS, Q.AssignmentEndDate, Employees.QuitReason,
Employees_1.[Employee Name] FROM (Employees RIGHT JOIN [SELECT A.Post AS
Post, A.AssignmentEndDate, A.AssignedEmployeeSS AS SS FROM Assignments
AS
A;]. AS Q ON (Employees.EndDate = Q.AssignmentEndDate) AND (Employees.SS
=
Q.SS)) INNER JOIN Employees AS Employees_1 ON Q.SS = Employees_1.SS
WHERE
(((Q.Post)= '" & cmbPost.Value & "') AND ((Q.AssignmentEndDate) Is Not
Null))"
Reports(stDocName).RecordSource = SQLquery

Try it like this:

SQLquery = "SELECT Q.SS, Q.AssignmentEndDate, Employees.QuitReason,
Employees_1.[Employee Name] FROM (Employees RIGHT JOIN (SELECT A.Post AS
Post, A.AssignmentEndDate, A.AssignedEmployeeSS AS SS FROM Assignments AS
A) AS Q ON (Employees.EndDate = Q.AssignmentEndDate) AND (Employees.SS =
Q.SS)) INNER JOIN Employees AS Employees_1 ON Q.SS = Employees_1.SS WHERE
(((Q.Post)= '" & cmbPost.Value & "') AND ((Q.AssignmentEndDate) Is Not
Null))"
Reports(stDocName).RecordSource = SQLquery
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

STRSQL = "SELECT Q.SS, Q.AssignmentEndDate, Employees.QuitReason" & _
", Employees_1.[Employee Name] " & _
" FROM (Employees RIGHT JOIN " & _
" (SELECT A.Post AS Post, A.AssignmentEndDate" & _
", A.AssignedEmployeeSS AS SS " & _
" FROM Assignments AS A) AS Q " & _
" ON Employees.SS=Q.SS AND Employees.EndDate=Q.AssignmentEndDate)" & _
" INNER JOIN Employees AS Employees_1 ON Q.SS=Employees_1.SS " & _
" WHERE Q.Post='Envelope Repair' And Q.AssignmentEndDate Is Not Null"

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Pato-chan said:
At least I get a different error message with this. It now says I have an
extra parenthesis and highlights the last closing parenthesis before the
Is
Not Null statement.

John Spencer said:
I would think that the following SQL would work

STRSQL = "SELECT Q.SS, Q.AssignmentEndDate, Employees.QuitReason" & _
", Employees_1.[Employee Name] " & _
" FROM (Employees RIGHT JOIN " & _
" (SELECT A.Post AS Post, A.AssignmentEndDate" & _
", A.AssignedEmployeeSS AS SS " & _
" FROM Assignments AS A) AS Q " & _
" ON Employees.SS=Q.SS AND Employees.EndDate=Q.AssignmentEndDate)" &
_
" INNER JOIN Employees AS Employees_1 ON Q.SS=Employees_1.SS " & _
" WHERE Q.Post='Envelope Repair' And Q.AssignmentEndDate) Is Not Null"


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Pato-chan said:
Then I get a syntax error in the FROM clause because the jet engine
changed
the recordsource to be as follows:

SELECT Q.SS, Q.AssignmentEndDate, Employees.QuitReason,
Employees_1.[Employee Name] FROM (Employees RIGHT JOIN [SELECT A].[Post
AS
Post, A].[AssignmentEndDate, A].[AssignedEmployeeSS AS SS FROM
Assignments
AS
A; ] AS Q ON (Employees.SS=Q.SS) AND
(Employees.EndDate=Q.AssignmentEndDate))
INNER JOIN Employees AS Employees_1 ON Q.SS=Employees_1.SS WHERE
(((Q.Post)='Envelope Repair') And ((Q.AssignmentEndDate) Is Not Null));

:

On Wed, 9 Jan 2008 09:18:02 -0800, Pato-chan

My SQL code inside the VB code looks as follows:

SQLquery = "SELECT Q.SS, Q.AssignmentEndDate, Employees.QuitReason,
Employees_1.[Employee Name] FROM (Employees RIGHT JOIN [SELECT A.Post
AS
Post, A.AssignmentEndDate, A.AssignedEmployeeSS AS SS FROM
Assignments
AS
A;]. AS Q ON (Employees.EndDate = Q.AssignmentEndDate) AND
(Employees.SS
=
Q.SS)) INNER JOIN Employees AS Employees_1 ON Q.SS = Employees_1.SS
WHERE
(((Q.Post)= '" & cmbPost.Value & "') AND ((Q.AssignmentEndDate) Is
Not
Null))"
Reports(stDocName).RecordSource = SQLquery

Try it like this:

SQLquery = "SELECT Q.SS, Q.AssignmentEndDate, Employees.QuitReason,
Employees_1.[Employee Name] FROM (Employees RIGHT JOIN (SELECT A.Post
AS
Post, A.AssignmentEndDate, A.AssignedEmployeeSS AS SS FROM Assignments
AS
A) AS Q ON (Employees.EndDate = Q.AssignmentEndDate) AND (Employees.SS
=
Q.SS)) INNER JOIN Employees AS Employees_1 ON Q.SS = Employees_1.SS
WHERE
(((Q.Post)= '" & cmbPost.Value & "') AND ((Q.AssignmentEndDate) Is Not
Null))"
Reports(stDocName).RecordSource = SQLquery
Click to expand...
Click to expand...
Click to expand...
 
P

Pato-chan

When I take out what jet thinks is the extra parenthesis the engine turns my
recordsource SQL query into the following:

SELECT Q.SS, Q.AssignmentEndDate, Employees.QuitReason,
Employees_1.[Employee Name] FROM (Employees RIGHT JOIN [SELECT A].[Post AS
Post, A].[AssignmentEndDate, A].[AssignedEmployeeSS AS SS FROM Assignments AS
A; ] AS Q ON (Employees.EndDate=Q.AssignmentEndDate) AND (Employees.SS=Q.SS))
INNER JOIN Employees AS Employees_1 ON Q.SS=Employees_1.SS WHERE
Q.Post='Envelope Repair' And Q.AssignmentEndDate Is Not Null;

John Spencer said:
I would think that the following SQL would work

STRSQL = "SELECT Q.SS, Q.AssignmentEndDate, Employees.QuitReason" & _
", Employees_1.[Employee Name] " & _
" FROM (Employees RIGHT JOIN " & _
" (SELECT A.Post AS Post, A.AssignmentEndDate" & _
", A.AssignedEmployeeSS AS SS " & _
" FROM Assignments AS A) AS Q " & _
" ON Employees.SS=Q.SS AND Employees.EndDate=Q.AssignmentEndDate)" & _
" INNER JOIN Employees AS Employees_1 ON Q.SS=Employees_1.SS " & _
" WHERE Q.Post='Envelope Repair' And Q.AssignmentEndDate) Is Not Null"


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Pato-chan said:
Then I get a syntax error in the FROM clause because the jet engine
changed
the recordsource to be as follows:

SELECT Q.SS, Q.AssignmentEndDate, Employees.QuitReason,
Employees_1.[Employee Name] FROM (Employees RIGHT JOIN [SELECT A].[Post AS
Post, A].[AssignmentEndDate, A].[AssignedEmployeeSS AS SS FROM Assignments
AS
A; ] AS Q ON (Employees.SS=Q.SS) AND
(Employees.EndDate=Q.AssignmentEndDate))
INNER JOIN Employees AS Employees_1 ON Q.SS=Employees_1.SS WHERE
(((Q.Post)='Envelope Repair') And ((Q.AssignmentEndDate) Is Not Null));

Michael Gramelspacher said:
On Wed, 9 Jan 2008 09:18:02 -0800, Pato-chan

My SQL code inside the VB code looks as follows:

SQLquery = "SELECT Q.SS, Q.AssignmentEndDate, Employees.QuitReason,
Employees_1.[Employee Name] FROM (Employees RIGHT JOIN [SELECT A.Post AS
Post, A.AssignmentEndDate, A.AssignedEmployeeSS AS SS FROM Assignments
AS
A;]. AS Q ON (Employees.EndDate = Q.AssignmentEndDate) AND (Employees.SS
=
Q.SS)) INNER JOIN Employees AS Employees_1 ON Q.SS = Employees_1.SS
WHERE
(((Q.Post)= '" & cmbPost.Value & "') AND ((Q.AssignmentEndDate) Is Not
Null))"
Reports(stDocName).RecordSource = SQLquery

Try it like this:

SQLquery = "SELECT Q.SS, Q.AssignmentEndDate, Employees.QuitReason,
Employees_1.[Employee Name] FROM (Employees RIGHT JOIN (SELECT A.Post AS
Post, A.AssignmentEndDate, A.AssignedEmployeeSS AS SS FROM Assignments AS
A) AS Q ON (Employees.EndDate = Q.AssignmentEndDate) AND (Employees.SS =
Q.SS)) INNER JOIN Employees AS Employees_1 ON Q.SS = Employees_1.SS WHERE
(((Q.Post)= '" & cmbPost.Value & "') AND ((Q.AssignmentEndDate) Is Not
Null))"
Reports(stDocName).RecordSource = SQLquery
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How would I combine or nest these two SQL queries into one? 4
Subquery can only return one record when used inside the WHERE cla 2
SELECT in a JOIN query results in 'Syntax Error in FROM clause' 5
Grouping and Aggregating Data in Jet SQL 1
Help with Sql statement 8
Totals in crosstab query 0
The Many Flavors of SQL - Can a SQL Server query work in MS Access? 6
Grouping and Aggregating Data in Jet SQL 0

Top