访问OpenReport不适用于Where条件 [英] Access OpenReport not working with Where condtion

问题描述

这必须是一个非常简单的错误.我只是看不到. 我的where字符串:

This must be a very simple mistake. I just can't see it. My Where String:

strWhere = "tbl_final.[Geschäftspartner] = " & Chr(34) & BP & Chr(34)

应该去哪儿?

DoCmd.OpenReport "Contribution", acViewPreview, , strWhere

结果是该报告已打开...但包含所有记录.我一直在研究不同版本的SQL字符串，甚至为将参数引入基础查询(我知道这不是解决方案)都花了很长时间.

Result is that the report opens... but with all records. I've been playing around with different versions of the SQL string for quite a while even going so far as to introduce parameters to the underlying query (which I know is NOT the solution).

我没看到什么?

更新: 基础查询是直接嵌入到报表的RecordSource属性中的SQL语句

UPDATE: The underlying query is an SQL statement embedded directly in the reports RecordSource property

UPDATE2: 如果我创建一个仅包含字段Geschäftspartner和一个度量标准字段的全新报表，则该代码有效.因此，该错误必定是由报告中的某些内容引起的.

UPDATE2: If I create a completely new Report that contains only the fields Geschäftspartner and one metric field then the code works. Thus the error must be caused by something in the report.

UPDATE3:链接到报告的完整查询

UPDATE3: Full Query that is linked to report

SELECT tbl_final.Geschäftspartner, tbl_final.Referenzwährung,    tbl_final.Periodenbeginn, tbl_final.Periodenende, tbl_final.Anlagekategorie,   tbl_final.[Produkt / ISIN], tbl_final.ISIN, tbl_final.[Telekurs Valorennummer],   tbl_final.[Asset Domicile], tbl_final.[Valor name], tbl_final.[FIRE: Kontonummer], tbl_final.[Special Fund Category], tbl_final.[FRS: Type of Collateral], tbl_final.[Nominal Currency], tbl_final.[Asset Type], tbl_final.[Anteile per Periodenende], tbl_final.[Marktwert per Periodenende], tbl_final.[Durchschn# Marktwert  in Referenzwährung], tbl_final.[Durchschnittlicher Anteil in %], tbl_final.[AVA_TER in %], tbl_final.[AVA_TER Datum], tbl_final.[VDF_TER in %], tbl_final.[VDF_TER Datum], tbl_final.[TER in %], tbl_final.[TER Datum], tbl_final.[Gewichtete TER in %], tbl_final.[TER in Referenzwährung], tbl_final.Client, tbl_final.CRM, tbl_final.[Received TER-Report for 2014], tbl_final.Language
FROM tbl_final;

推荐答案

在经历了SQL语句并确保它在虚拟表，虚拟查询和虚拟报告上正常运行之后，我最后回到了我的VBA代码.

After having gone through the SQL statement and ensured that it works properly on a dummy table, dummy query, and with a dummy report, I ended up going back to my VBA code.

我在那里发现了问题:

我编写了代码，以编程方式更改了报表上的标签，然后使用Where Condition将其打开.我有以下几行代码:

I had written code that changed labels on the report programmatically before opening it with the Where Condition. I had the following lines of code:

DoCmd.OpenReport REPORT_NAME, acDesign
Set rptObject = Reports(REPORT_NAME)

'Various other objects being changed here but edited out for readability
'...
rptObject.OLEUnbound86.Visible = True

DoCmd.Close acForm, REPORT_NAME, acSaveYes

在最后一行中，我没有关闭REPORT，而是关闭了FORM.不存在的一种.不幸的是，Access从未使我意识到这一事实！因此，报表保持打开状态，当我尝试通过Where条件时，它没有将其应用于报表(大概是因为对象仍处于打开状态).

In the last line, instead of closing a REPORT, I was closing a FORM. One that doesn't exist. Unfortunately, Access never alerted me to this fact! Thus, the report remained open, and when I tried to pass the Where Condition, it didn't apply it to the report (presumably because the object was still open).

解决方案是将最后一行简单地更改为:

The solution was to simply change that last line to:

DoCmd.Close acReport, REPORT_NAME, acSaveYes

这篇关于访问OpenReport不适用于Where条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！