Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Posts
    2

    Unanswered: nested sql - invalid bracketing

    Evening all,

    Here's one of the things which frustrate me about MSAccess. I write a query such as:

    SELECT *
    INTO newtable
    FROM (
    SELECT ADJID, ADHNO, DOOJA, NAME1, SURNAME, [FINAL CONSENT STATUS], PPP
    FROM T1
    UNION
    SELECT ADJID, ADHNO, DOOJA, NAME1, SURNAME, [FINAL CONSENT STATUS], PPP
    FROM T2
    UNION
    SELECT ADJID, ADHNO, DOOJA, NAME1, SURNAME, [FINAL CONSENT STATUS], PPP
    FROM T3
    )
    ORDER BY ADHNO

    which Access is quite happy to run. I close the query and open it again and Access has changed it to:

    SELECT *
    FROM [SELECT ADJID, ADHNO, DOOJA, NAME1, SURNAME, [FINAL CONSENT STATUS], PPP
    FROM T1
    UNION
    SELECT ADJID, ADHNO, DOOJA, NAME1, SURNAME, [FINAL CONSENT STATUS], PPP
    FROM T2
    UNION
    SELECT ADJID, ADHNO, DOOJA, NAME1, SURNAME, [FINAL CONSENT STATUS], PPP
    FROM T3
    ]. AS [%$##@_Alias]
    ORDER BY ADHNO;

    replacing my ( ) with [ ]. AS [%$##@_Alias]. I didn't ask it to do that! So I run the query now and get the error message :
    "Invalid bracketing of name 'SELECT ADJID, ADHNO, DOOJA, NAME1, SURNAME, [FINAL CONSENT STATUS'."
    , so I have to edit the query to return it to the form that I left it before Bill Gates got his mucky paws on it so that I can run it again.

    Does anyone know why?
    Does anyone have a solution?

    Many thanks

  2. #2
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Thumbs down there is 1 thing you can do

    and thats to set your SQL statment in code
    but i dont think thats the answer your looking 4

    ive got the same prob if you have a query with 50 + lines
    and you order them neetly like

    SELECT field1,field2,field3
    FROM Table
    WHERE field1 = X
    ORDER by field2

    Access change it to

    SELECT field1,field2,field3 FROM Table WHERE field1 = X ORDER by field2

  3. #3
    Join Date
    Nov 2003
    Posts
    2
    Many thanks,

    MS is often confident that it knows better than you what you want - like your example and Access's habit of putting at least two sets of un-needed brackets around a WHERE phrase (e.g. <WHERE ((([F1])="p"))>, which works the same as <WHERE F1="p">). Most of these, however are cosmetic and, apart from making the code more difficult to read, don't affect the running of the query.

    I often use VBA to run SQL code, but it's usually easier to develop in the query SQL view.

    Never mind - I've already got used to it, just bugs me now and then.

    Thanks
    R

  4. #4
    Join Date
    Nov 2002
    Posts
    49
    dbrog,

    I have to agree that it's easier to create your vba query in the query design view, then grab the SQL statement that MS Access provides and incorporate it into vba. That way, I can test my querys and determine if I'm getting the expected results before I test the code. That's an even bigger pain in the $#@!! Anyway, I'm not familiar with the type of query you are trying to run, but it seems that your problem is too many SELECT's. Try eliminating one and see if you still have the problem with the invalid bracketing.

  5. #5
    Join Date
    Nov 2003
    Posts
    4
    I wouldn't have a problem if there was only one SELECT, but that would be because there wouldn't be a nested query, without which I wouldn't get the result that I want.

  6. #6
    Join Date
    Nov 2002
    Posts
    49
    Originally posted by dbrogwk
    I wouldn't have a problem if there was only one SELECT, but that would be because there wouldn't be a nested query, without which I wouldn't get the result that I want.
    I guess your right. Another option is to preform the first query, and take the resulting recordset and run it through the Select * into query. However, you would probably be better off preforming an AddNew recordset. I guess this all depends on what you are truely attempting to do. If you are using this table as a "Temporary" table, create the table and set up a DELETE query to empty the data when you would delete the table. This has worked for me in the past, but there may be better/other ways to do the same thing.

  7. #7
    Join Date
    Nov 2003
    Posts
    4
    yes, there are a few long ways round this, but an advantage of nesting an SQL statement within another and generating a temporary recordset is that you have to run only one query and there is no tidying up to do at the end. This kind of query is also handy because it take seconds to throw together a query which might otherwise take several seperate queries using each other to run.

    Anyway, I was just having a moan about Access and now it's time to go home. hoe you all have a good weekend.

    R

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •