Access is re-writing - and breaking - my query!
- by FrustratedWithFormsDesigner
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.
