Filter within a report

[Andreas]

Hello all,

I created a report based on a query. I would like the report's output
to be adjusted according to filters that I use in the query. That is,
when I filter some records in the query, I also would like to only see
these particular records in my report. How can I achieve this with
only creating one single report?

Regards,
Andreas

 

[Armen Stein]

This is a pretty crude way of going about things though. A better approach
would be to have an unbound dialogue form in which you can build a filter
string, and from the form open either a form or report based on the query,
passing the filter string to it as the WhereCondition argument of the
OpenForm or OpenReport method.

I actually recommend calling the *report* first, which in turn calls
the unbound selection form from the report's Open event.

Open the form in Dialog mode so that the report waits for the form to
be closed or hidden before it proceeds. That way you can collect
criteria from the user and build a Where clause for the report. It
also means that you can call the report directly - you don't need to
call it from a form. And the selection form is reusable - it can be
called from multiple reports if they need the same criteria.

I've posted examples of this technique on our J Street Downloads page
at http://ow.ly/M58Y
See "Report Selection Techniques". We use this approach in almost all
of our projects.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[wangzhe]

从现在起 我们å¯ä»¥é‡æ–°å¼€å§‹

 

[wangzhe]

我担心我的计算机硬盘容é‡ä¸å¤Ÿã€‚所以删掉了一些看过了的邮件，你们能帮我扩å…硬盘å—？

 

[Armen Stein]

I do use a similar approach to that, but in my case I test for the form being
open in the report's Open event procedure, setting the return value of its
Cancel argument to True if not, then open the form, passing the name of the
report as the OpenArgs argument, and open the report from the form, passing
the filter as WhereCondition argument. Again this allows the same form to be
used to enter the criteria for multiple reports. The end result is the same,
though your approach might have a slight edge as the report is opened once
only.

Hi Ken,

Yes, your approach seems more complex, and I'm not sure I'm seeing the
benefit. In the Open event of the report, we just run an OpenForm in
dialog mode. We don't need to check if the form is already open, as
an OpenForm will just make an already-open form visible. This has the
added advantage of "remembering" the last criteria used during this
session. We open the report only once, and we don't need OpenArgs at
all.

Avoiding opening the report twice can be a big advantage when the
report is based on a passthrough query, because Access actually
evaluates the recordset *before* the Open event, which can be a big
performance issue. We've seen a delay of a few seconds before the
criteria form is displayed. To solve it, we had to leave a "Where
1=0" clause in the passthrough when the report closes. I have a slide
on this toward the end of "Best of Both Worlds" at http://ow.ly/M2WI.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com