Re: Access Changes SQL and makes unusable

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi Aaron And Klatuu
Thanks for your help and comments
WHERE [surname]= [Enter Name]
works every time!!!

Thanks Again and Best Regards
<dbahooker@xxxxxxxxxxx> wrote in message
news:1163624874.518237.6670@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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




Klatuu wrote:
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" wrote:

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






.

Relevant Pages

  • Re: PHP query using WHERE with AND
    ... AND (pictures.markername LIKE $surname) ... Just picture it in context of the full query. ... the LIKE operator in SQL is mostly useless without at least ... Worse, failure to escape criteria can lead to SQL injection, ...
    (alt.php)
  • Re: PHP query using WHERE with AND
    ... Just picture it in context of the full query. ... the LIKE operator in SQL is mostly useless without at least ... imagine the following query when $surname ... Worse, failure to escape criteria can lead to SQL injection, ...
    (alt.php)
  • Access Changes SQL and makes unusable
    ... In an earlier post I asked for help on some SQL code. ... The code works fine until I try to change the surname from SMITH, To say, ... FROM school INNER JOIN [SELECT class FROM school ... and no modification is possible except re-writing the ...
    (microsoft.public.access.forms)
  • Re: Query help required
    ... How are you changing the surname: in the SQL view, ... Doug Steele, Microsoft Access MVP ... Query works fine until..... ... FROM school INNER JOIN [SELECT class FROM school ...
    (microsoft.public.access.forms)
  • Re: Access Changes SQL and makes unusable
    ... it doesn't happen because you open the query in design view. ... In an earlier post I asked for help on some SQL code. ... The code works fine until I try to change the surname from SMITH, To say, ... FROM school INNER JOIN [SELECT class FROM school ...
    (microsoft.public.access.forms)