SQL restructured incorrectly by MS Access

  • Thread starter Thread starter Daniel S
  • Start date Start date
D

Daniel S

I have created a query in SQL that performs a left join
on a table of students and a select statement

The query works correctly when viewed on screen, but when
I try to use the query as the record source of a report,
it is rewritten by MS Access, and subseqentially doesn't
work.

Any ideas how to get round this one!
 
Maybe....

but can you tell us what the query's SQL statement is? We have no idea of
what you wrote and what ACCESS has done with it.

Post your original SQL and what it is in the recordsource of the report.
 
Daniel S said:
I have created a query in SQL that performs a left join
on a table of students and a select statement

The query works correctly when viewed on screen, but when
I try to use the query as the record source of a report,
it is rewritten by MS Access, and subseqentially doesn't
work.

Any ideas how to get round this one!
Click to expand...


Daniel S.,

Please post your SQL attempts, your sample input data, sample expected
output data, and table structures including column data types and sizes,
indexing, and relationships.

Please include the exact text of the error message, including exactly when
the error message appeared and what was going on when it appeared.

This will help anyone here who wants to help greatly. :)


Sincerely,

Chris O.
 
This is the original query as written in SQL
-----------
SELECT qryStudents.*, sqlEvents.*
FROM qryStudents
LEFT JOIN (SELECT tblEvents.* FROM tblEvents WHERE
[EventDate] >= #25/02/2004#)
AS sqlEvents
ON qryStudents.StudentID = sqlEvents.StudentID
WHERE [OffRoll] = False

This is how Access rewrites it after saving the query
--------
SELECT qryStudents.*, sqlEvents.*
FROM qryStudents LEFT JOIN [SELECT tblEvents.* FROM
tblEvents WHERE [EventDate] >= #25/02/2004#]. AS
sqlEvents ON qryStudents.StudentID = sqlEvents.StudentID
WHERE [OffRoll] = False;

This is how Access rewrites it when I have directly
entered it as the record source for the report.

SELECT qryStudents.*, sqlEvents.* FROM qryStudents LEFT
JOIN [SELECT tblEvents].[* FROM tblEvents WHERE
[EventDate] >= #25/02/2004#] AS sqlEvents ON
[qryStudents].[StudentID]=sqlEvents.StudentID WHERE
[OffRoll]=False;

When I try and use this as the basis of a report it fails
to work producing the following message
"Syntax Error in From Clause"

If I modify the SQL that Access has written (e.g.
changing the date) the error message also appears.

The purpose of the report is to provide a count of
different types of event within a certain time scale
whilst including those students for whom there are no
events.
I know here are other ways of providing this data, one of
which I have used (Creating a union of all those students
with no events, with those with events and then filtering
to include Null dates as well as those meeting the
criteria.

It is more a request to see if anybody knows a way to
prevent Access modifying the SQL, rather than finding a
workaround

What I find really strange is that if I open the new SQL
statement as written by Access, which is producing the
correct result when viewing the query, copy the SQL
statement and then paste it directly back in, it doesn't
work!
 
What you see is the "normal" behavior for ACCESS with this type of query.
ACCESS translates FROM-clause subqueries in the standard parenthetical form
you started with into the "[subquery]. " form, and any square bracket inside
the subquery's enclosing brackets will interfere with the correct
interpretation of it in VBA. Even a bracketed
.[field] reference
will cause the problem.

The "[subquery]. " form will work in a stored query, but not if you try to
use it in VBA.

The only way to avoid this is to rewrite the SQL in a way that this syntax
is not needed.


--
Ken Snell
<MS ACCESS MVP>




Daniel S said:
This is the original query as written in SQL
-----------
SELECT qryStudents.*, sqlEvents.*
FROM qryStudents
LEFT JOIN (SELECT tblEvents.* FROM tblEvents WHERE
[EventDate] >= #25/02/2004#)
AS sqlEvents
ON qryStudents.StudentID = sqlEvents.StudentID
WHERE [OffRoll] = False

This is how Access rewrites it after saving the query
--------
SELECT qryStudents.*, sqlEvents.*
FROM qryStudents LEFT JOIN [SELECT tblEvents.* FROM
tblEvents WHERE [EventDate] >= #25/02/2004#]. AS
sqlEvents ON qryStudents.StudentID = sqlEvents.StudentID
WHERE [OffRoll] = False;

This is how Access rewrites it when I have directly
entered it as the record source for the report.

SELECT qryStudents.*, sqlEvents.* FROM qryStudents LEFT
JOIN [SELECT tblEvents].[* FROM tblEvents WHERE
[EventDate] >= #25/02/2004#] AS sqlEvents ON
[qryStudents].[StudentID]=sqlEvents.StudentID WHERE
[OffRoll]=False;

When I try and use this as the basis of a report it fails
to work producing the following message
"Syntax Error in From Clause"

If I modify the SQL that Access has written (e.g.
changing the date) the error message also appears.

The purpose of the report is to provide a count of
different types of event within a certain time scale
whilst including those students for whom there are no
events.
I know here are other ways of providing this data, one of
which I have used (Creating a union of all those students
with no events, with those with events and then filtering
to include Null dates as well as those meeting the
criteria.

It is more a request to see if anybody knows a way to
prevent Access modifying the SQL, rather than finding a
workaround

What I find really strange is that if I open the new SQL
statement as written by Access, which is producing the
correct result when viewing the query, copy the SQL
statement and then paste it directly back in, it doesn't
work!


-----Original Message-----
I have created a query in SQL that performs a left join
on a table of students and a select statement

The query works correctly when viewed on screen, but when
I try to use the query as the record source of a report,
it is rewritten by MS Access, and subseqentially doesn't
work.

Any ideas how to get round this one!
.
Click to expand...
Click to expand...
 
Remove the brackets around EventDate in your Sub-query. Also, I would be very
careful with your date format. Access JET requires dates to be in US format of
MM/dd/YYYY. Since 25/02/04 is unambiguous Access will see it as 25 Feb 2004,
but 11/02/04 will be seen as 2 November 2004.

SELECT qryStudents.*, sqlEvents.*
FROM qryStudents
LEFT JOIN (SELECT tblEvents.* FROM tblEvents WHERE
EventDate >= #25/02/2004#)
AS sqlEvents
This is the original query as written in SQL
-----------
SELECT qryStudents.*, sqlEvents.*
FROM qryStudents
LEFT JOIN (SELECT tblEvents.* FROM tblEvents WHERE
[EventDate] >= #25/02/2004#)
AS sqlEvents
ON qryStudents.StudentID = sqlEvents.StudentID
WHERE [OffRoll] = False

This is how Access rewrites it after saving the query
--------
SELECT qryStudents.*, sqlEvents.*
FROM qryStudents LEFT JOIN [SELECT tblEvents.* FROM
tblEvents WHERE [EventDate] >= #25/02/2004#]. AS
sqlEvents ON qryStudents.StudentID = sqlEvents.StudentID
WHERE [OffRoll] = False;

This is how Access rewrites it when I have directly
entered it as the record source for the report.

SELECT qryStudents.*, sqlEvents.* FROM qryStudents LEFT
JOIN [SELECT tblEvents].[* FROM tblEvents WHERE
[EventDate] >= #25/02/2004#] AS sqlEvents ON
[qryStudents].[StudentID]=sqlEvents.StudentID WHERE
[OffRoll]=False;

When I try and use this as the basis of a report it fails
to work producing the following message
"Syntax Error in From Clause"

If I modify the SQL that Access has written (e.g.
changing the date) the error message also appears.

The purpose of the report is to provide a count of
different types of event within a certain time scale
whilst including those students for whom there are no
events.
I know here are other ways of providing this data, one of
which I have used (Creating a union of all those students
with no events, with those with events and then filtering
to include Null dates as well as those meeting the
criteria.

It is more a request to see if anybody knows a way to
prevent Access modifying the SQL, rather than finding a
workaround

What I find really strange is that if I open the new SQL
statement as written by Access, which is producing the
correct result when viewing the query, copy the SQL
statement and then paste it directly back in, it doesn't
work!
-----Original Message-----
I have created a query in SQL that performs a left join
on a table of students and a select statement

The query works correctly when viewed on screen, but when
I try to use the query as the record source of a report,
it is rewritten by MS Access, and subseqentially doesn't
work.

Any ideas how to get round this one!
.
Click to expand...
Click to expand...
 
Back
Top