Sub-Query headaches

I keep having problem with sub-queries and I hope someone can tell me why and what to do to resolve this ongoing problem once and for all!

The sub-query itself does not matter.  When I create it, it will have the general format

SELECT Tmp1.[Field1Name], ...
FROM (

SELECT ...

) AS Tmp1

 

If I save it and run it all is good.

 

Now, for unknow reasons (perhaps compact and repair, I don't know), I get error messages and when I open the query in design mode my query has been transformed from () brackets to [] and an extra . is added making the query not work, if I edit it back to its original form, it works again....   But this keeps happening in multiple databases!!!

SELECT Tmp1.[Field1Name], ...
FROM [

SELECT ...

]. AS Tmp1

 

Please help, someone!  Am I writing my queries wrong in the first place?  What am I doing wrong?

I am running WinXP and Office 2003 SP3  (everything is supposedly up-to-date).

Thank you!

QuestionBoy

This is a well-known and annoying problem.  Access has a preferred, non-standard SQL syntax for derived tables, and if you give it half a chance, it will transform your SQL into that syntax.  The standard syntax is:

    (SELECT ... FROM ...) As T

Jet's preferred, but non-standard syntax is:

    [SELECT ... FROM ...;]. As T

.. or some variation thereof.  As you see, the parentheses have been changed to square brackets, and a dot (.) has been added after the closing bracket.

Now, this doesn't matter if the subquery doesn't include any square brackets of its own -- around field or table names, for example.  But sometime those are required because of names that use nonstandard characters, and also the Access query designer automatically surrounds everything with brackets "for safety's sake."  So in these cases, the transformed query becomes syntactically invalid, because Access can't parse brackets inside of brackets.  Once this happens, if the SQL has to be reparsed, an error is raised.

The first workaround for this problem is to ensure that no brackets are present around field names or table names in the SQL of the subquery.  If you can do that, then there will be no problem if Access decides to rewrite your SQL in its own quirky way.

If you can't do that, then you need to design the query in SQL view, and try never to open the query in design view.  If you do open it in design view, you must not then save the query, unless you first switch to design view and fix the SQL again.

This is particularly a problem in rowsources for combo and list boxes, since clicking the build button on the RowSource property line automatically opens the query in design view.  Flip to SQL view, fix it if necessary, and do your work there.

We have asked Microsoft to fix this bug in the query designer -- so far, to no effect.