Access is re-writing - and breaking - my query!

Question

I have a query in MS Access (2003) that makes use of a subquery. The subquery part looks like this:

...FROM (SELECT id, dt, details FROM all_recs WHERE def_cd="ABC-00123") AS q1,...

And when I switch to Table View to verify the results, all is OK.

Then, I wanted the result of this query to be printed on the page header for a report (the query returns a single row that is page-header stuff). I get an error because the query is suddenly re-written as:

...FROM [SELECT id, dt, details FROM all_recs WHERE def_cd="ABC-00123"; ] AS q1,...

So it's Ok that the round brackets are automatically replaced by square brackets, Access feels it needs to do that, fine! But why is it adding the ; into the subquery, which causes it to fail?

I suppose I could just create new query objects for these subqueries, but it seems a little silly that I should have to do that.

Answer 1

Ah, the joys of Access. The query designer in general does not play well with derived tables. There are more than a few constructs in fact, that Jet will honor that cannot be viewed properly in the query designer. In fact, the QBE will mangle (alter as you have seen) many of these complex queries. In general, you should simply assume that you cannot safely view the design of a derived table or "complex" query in the QBE but instead only in code.

Answer 2

I have seen what you described where Access replaces subquery parentheses with square brackets. However I have never noticed it adding in a semicolon after the subquery.

Another detail is that, with square backets, your query will follow this pattern:

... FROM [ SELECT whatever FROM someTable ]. AS q ...

Notice the dot immediately after the closing square bracket. Your sample didn't include a dot. So I wonder what might happen if you add the dot and remove the semicolon (in SQL View) like this:

...FROM [SELECT id, dt, details FROM all_recs WHERE def_cd="ABC-00123" ]. AS q1,...

Does Access accept that change, and is that preserved when you make any further changes through the Query Designer?

Answer 3

If you want to use the more standard derived-table syntax, you need to switch to SQL 92 mode. However, beware that this also changes your wildcards to SQL-Server be compatible (% and _ instead of * and ?).

As @HansUp points out, the error in your SQL is not the ";" but the lack of the trailing period after the closing square bracket. That syntax has been part of Jet for as long as I've been using derived tables (which would be back to A97 or so). It has the flaw of preventing any expressions inside the derived-table SQL that require square brackets (such as field names with spaces in them), but I don't think that's a terrible flaw as I avoid naming things in ways that require square brackets.