Jet finds a syntax error in my SQL statement

[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));

 

[John W. Vinson]

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));

Could you post the actual VBA code you're using? SQL statements in VBA must be
Strings - you can't just mix SELECT clauses inline with VBA statements.

Since you're joining - apparently - to an inline select query, you may need to
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.

John W. Vinson [MVP]

 

[Pato-chan]

My VB code is 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

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));

Could you post the actual VBA code you're using? SQL statements in VBA must be
Strings - you can't just mix SELECT clauses inline with VBA statements.

Since you're joining - apparently - to an inline select query, you may need to
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.

John W. Vinson [MVP]