Access Changes SQL and makes unusable

  • Thread starter Thread starter SqlDope
  • Start date Start date
S

SqlDope

Hello, --Ms Access 2003 SP2--
In an earlier post (Query Help Required) I asked for help on some SQL code.
The answer was supplied by Douglas J. Steele.
As Below:
SELECT DISTINCT School.Class, School.Forename, School.Surname,
School.Date,
School.Age, School.year, School.ref
FROM School INNER JOIN
(
SELECT Class FROM School
WHERE [surname]="SMITH"
) AS Subquery
ON [School].[Class]=Subquery.Class
ORDER BY School.Class;

The code works fine until I try to change the surname from SMITH, To say,
BROWN
I get an error which says syntax error in FROM clause.
On viewing the code In SQL view I Find that the code has changed to:

SELECT DISTINCT school.class, school.Forename, school.Surname, school.Date,
school.Age, school.year, school.ref
FROM school INNER JOIN [SELECT class FROM school
WHERE [surname]="smith"
]. AS Subquery ON [school].[class]=Subquery.class
ORDER BY school.class;

and no modification is possible except re-writing the
first peice of code with a different surname.

And so each time I wish to run the query with a different
surname I have to re-write the whole code
BTW I'm not using a form but changing the surname in SQL view
Any further help would be greatly appreciated
Best Regards
 
That happens because you open the query in design view. The query builder
optimizes and compiles the query you build in the query builder for best
performance, regardless of how you write it.

I don't know if this will work and I can't test it at the moment, but I
would try changing the hardcoded name to a parameter.

WHERE [surname]= [Enter Name]

Then, if it works, when you run the query, it will pop up a box asking for
the name.
 
no.. it doesn't happen because you open the query in design view.

it happens because Microsoft releases BUGGY ASS SOFTWARE.
AND THEY WONT FIX BUGS.

I've had Access break sql statements probably 10% of the time that I
use it; so I just reccomend not using MDB databases for any reason
whatsoever

-Aaron



That happens because you open the query in design view. The query builder
optimizes and compiles the query you build in the query builder for best
performance, regardless of how you write it.

I don't know if this will work and I can't test it at the moment, but I
would try changing the hardcoded name to a parameter.

WHERE [surname]= [Enter Name]

Then, if it works, when you run the query, it will pop up a box asking for
the name.


SqlDope said:
Hello, --Ms Access 2003 SP2--
In an earlier post (Query Help Required) I asked for help on some SQL code.
The answer was supplied by Douglas J. Steele.
As Below:
SELECT DISTINCT School.Class, School.Forename, School.Surname,
School.Date,
School.Age, School.year, School.ref
FROM School INNER JOIN
(
SELECT Class FROM School
WHERE [surname]="SMITH"
) AS Subquery
ON [School].[Class]=Subquery.Class
ORDER BY School.Class;

The code works fine until I try to change the surname from SMITH, To say,
BROWN
I get an error which says syntax error in FROM clause.
On viewing the code In SQL view I Find that the code has changed to:

SELECT DISTINCT school.class, school.Forename, school.Surname, school.Date,
school.Age, school.year, school.ref
FROM school INNER JOIN [SELECT class FROM school
WHERE [surname]="smith"
]. AS Subquery ON [school].[class]=Subquery.class
ORDER BY school.class;

and no modification is possible except re-writing the
first peice of code with a different surname.

And so each time I wish to run the query with a different
surname I have to re-write the whole code
BTW I'm not using a form but changing the surname in SQL view
Any further help would be greatly appreciated
Best Regards
Click to expand...
Click to expand...
 
Hi Aaron And Klatuu
Thanks for your help and comments
WHERE [surname]= [Enter Name]
works every time!!!

Thanks Again and Best Regards
no.. it doesn't happen because you open the query in design view.

it happens because Microsoft releases BUGGY ASS SOFTWARE.
AND THEY WONT FIX BUGS.

I've had Access break sql statements probably 10% of the time that I
use it; so I just reccomend not using MDB databases for any reason
whatsoever

-Aaron



That happens because you open the query in design view. The query
builder
optimizes and compiles the query you build in the query builder for best
performance, regardless of how you write it.

I don't know if this will work and I can't test it at the moment, but I
would try changing the hardcoded name to a parameter.

WHERE [surname]= [Enter Name]

Then, if it works, when you run the query, it will pop up a box asking
for
the name.


SqlDope said:
Hello, --Ms Access 2003 SP2--
In an earlier post (Query Help Required) I asked for help on some SQL
code.
The answer was supplied by Douglas J. Steele.
As Below:
SELECT DISTINCT School.Class, School.Forename, School.Surname,
School.Date,
School.Age, School.year, School.ref
FROM School INNER JOIN
(
SELECT Class FROM School
WHERE [surname]="SMITH"
) AS Subquery
ON [School].[Class]=Subquery.Class
ORDER BY School.Class;

The code works fine until I try to change the surname from SMITH, To
say,
BROWN
I get an error which says syntax error in FROM clause.
On viewing the code In SQL view I Find that the code has changed to:

SELECT DISTINCT school.class, school.Forename, school.Surname,
school.Date,
school.Age, school.year, school.ref
FROM school INNER JOIN [SELECT class FROM school
WHERE [surname]="smith"
]. AS Subquery ON [school].[class]=Subquery.class
ORDER BY school.class;

and no modification is possible except re-writing the
first peice of code with a different surname.

And so each time I wish to run the query with a different
surname I have to re-write the whole code
BTW I'm not using a form but changing the surname in SQL view
Any further help would be greatly appreciated
Best Regards
Click to expand...
Click to expand...
Click to expand...
 
Back
Top