Here is about the most frustrating error message you can get in Access (Okay, there are many others that are just as problematic…)

I myself had encountered this errors many times in the past and understood the reason (improper SQL statement), but never understood why after performing the necessary correction to the query to make it work, that it would break again?! As of late, I came accross an excellent explanation made by fellow MVP, Dirk Goldgar.

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.

Feel free to checkout the original post at: http://answers.microsoft.com/en-us/office/forum/office_2003-access/sub-query-headaches/b1e09348-209f-4a2f-acc1-fe7191ae9591

So what have we learnt, yet again? The importance of following naming conventions. In my particular case, like the OP in the original post, I am working with an old database that was developed by someone else and am now trying to fix. So I will have to live with this nuisance until I can do a cleanup of table field names, etc. But the entire issue can easily be entirely avoided by following a simple naming convention.

Lastly, thank you Dirk for the excellent, plain English explanation!