Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Mar 2002
    Location
    Buenos Aires, Argentina
    Posts
    877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Tidying Up Access SQL (2003)

    Hi,

    I've just moved to a new job and it seems I'll be using Access a lot more than I'd like to (we don't have SQL Server licenses at the moment).
    So I was just wondering if you had a few neat tips for coding using the SQL View.

    First off: there is no way to comment a query, right?

    Secondly: how do you indent your query? Tabs don't seem to work. I tried using ten spaces to substitute them, but Access seems to remove the spaces when I close and reopen the query.
    Third: is there any way to prevent Access from replacing brackets "( )" with square brackets "[ ]"? I don't quite remember now, but I guess when the query contained a subquery in the form:

    Select *
    from (subquery) as subqueryname;

    on query save and close Access would keep:

    Select *
    from [subquery] as subqueryname;

    or something.

    Fourth: any other suggestions?

    I hadn't polished these aspects yet as I had rather occasional need to use Access SQL, but now the time has come when coding more properly will make my life happier.

    Thank you all in advance
    <img src=/w3timages/blue3line.gif width=33% height=2>
    <img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Tidying Up Access SQL (2003)

    1) No, comments are not possible.
    2) No, indenting is not possible.
    3) No reason to bother.
    4) Create a query in design view as much as possible, and only switch to SQL view when you really need it.

  3. #3
    5 Star Lounger
    Join Date
    Mar 2002
    Location
    Buenos Aires, Argentina
    Posts
    877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tidying Up Access SQL (2003)

    Hello Hans,

    Thanks for your promptness!!

    The reason why I bother in 3) is that I remember having written a complex query with many subqueries. Some time later I needed to edit it, and when my edition was complete I tried saving the query but Access wouldn't let me. The cause of trouble was that Access had replaced the brackets with square brackets and conspicuously required me to restore them to normal brackets.
    <img src=/w3timages/blue3line.gif width=33% height=2>
    <img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Tidying Up Access SQL (2003)

    I guess something was wrong with your query, but without knowing details it's impossible to say what.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Tidying Up Access SQL (2003)

    Access did this consistently in A97, but became more accepting of inline subqueries in 2000 and above. Even in XP it would sometimes choke on the punctuation, but in that version, at least, it understood that square brackets followed by a period were acceptable punctuation for an inline subquery.
    Charlotte

  6. #6
    5 Star Lounger
    Join Date
    Mar 2002
    Location
    Buenos Aires, Argentina
    Posts
    877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tidying Up Access SQL (2003)

    Thanks Charlotte.

    Now that you mention the chronology I recall the version I was working with when I had that problem was 2000, so it's probably fixed in 2003 (and probably Hans was relying on my version being 2003 when he replied -- he can't be wrong!) <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    <img src=/w3timages/blue3line.gif width=33% height=2>
    <img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>

Posting Permissions

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