Re: Access Changes SQL and makes unusable

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

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: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
  • How do I do Paging through a large dataset via Stored Procedures
    ... Paging by dynamically altering the SQL Query ... Create stored procedures ... SELECT * FROM STUDENTS ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Populating a list -- learning Access
    ... It is kinda funny -- I started using databases in the early 80's with dBase and, for years, never knew that I knew SQL! ... If you are on a form or report, the most important property is the NAME, because that is how you refer to it in code. ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)
  • 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)