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

Posted by FrustratedWithFormsDesigner on Stack Overflow See other posts from Stack Overflow or by FrustratedWithFormsDesigner
Published on 2010-06-15T19:02:38Z Indexed on 2010/06/15 19:12 UTC
Read the original article Hit count: 207

Filed under:
|

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.

© Stack Overflow or respective owner

Related posts about sql

Related posts about ms-access