访问是重写-破坏了-我的查询！ [英] Access is re-writing - and breaking - my query!

问题描述

我在MS Access(2003)中有一个使用子查询的查询.子查询部分如下所示:

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,...

所以可以将圆括号自动替换为方括号，Access认为需要这样做，很好！但是为什么将;添加到子查询中，这会导致它失败?

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.

推荐答案

啊，Access的乐趣.通常，查询设计器不能很好地处理派生表.实际上有许多构造，Jet会尊重在查询设计器中无法正确查看的构造.实际上，QBE会粉碎(如您所见)许多这些复杂的查询.通常，您应该简单地假设您不能安全地在QBE中查看派生表或复杂"查询的设计，而只能在代码中查看.

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.

这篇关于访问是重写-破坏了-我的查询！的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！