Re: Am I using too many filters?

From: Brian (Brian_at_discussions.microsoft.com)
Date: 02/15/05 

Date: Mon, 14 Feb 2005 21:57:02 -0800

Actually, I was just wondering if there are better ways to approach reporting
than writing very basic reports and giving users the choice of many filters.
I think the concept of concatenating the various critieria via strings into
the Where string probably allows more flexibility than my purely
query-builder approach.

Thanks.

"Sylvain Lafontaine" wrote:

> You have answered yourself to your own question:
>
> « ... and yes, I did this with the query builder, not by manually
> writing the SQL »
>
> What kind of answer are you expecting from us?
>
> S. L.
>
> "Brian" <Brian@discussions.microsoft.com> wrote in message
> news:3BE1EAED-1DD2-4E63-966D-8490356432FB@microsoft.com...
> > For some reports, I prefer to write a single report and give the user up
> > to
> > ten or more filters (controls, usually combo boxes) on the form that calls
> > the report). I then drop a reference to the control name in the query's
> > criteria line to filter based on that control if it is populated.
> >
> > Since I have, by default, only nine criteria lines to a query, I sometimes
> > create two or three queries that each filter out different elements, and
> > then
> > do a join between them to apply ALL filters.
> >
> > However, I just ran into my first need to use a list box for one of the
> > criteria (or at least a need to stipulate "Equals" or "Does not equal" as
> > an
> > option), and the construction of the resultant query in VBA looks like a
> > garganuan task. Does anyone have a better approach to multi-filtered
> > reports,
> > or am I just crazy to try to offer this type of flexibility?
> >
> > Just as an example, here is the SQL view of one of three such stacked
> > queries that it takes to run one VERY flexible report, and yes, I did this
> > with the query builder, not by manually writing the SQL:
> >
> > SELECT Contracts.ContractID
> > FROM ReportContractBase1 INNER JOIN Contracts ON
> > ReportContractBase1.ContractID = Contracts.ContractID
> > WHERE (((Contracts.ContractID)=[Forms]![MenuReports]![ContractID]) AND
> > ((Contracts.ContractType)=[Forms]![MenuReports]![ContractType]) AND
> > ((Contracts.Status)<>[Forms]![MenuReports]![ContractStatus]) AND
> > (([Forms]![MenuReports]![ContractID]) Is Not Null) AND
> > (([Forms]![MenuReports]![ContractType]) Is Not Null) AND
> > (([Forms]![MenuReports]![ContractStatus]) Is Not Null)) OR
> > (((Contracts.ContractType)=[Forms]![MenuReports]![ContractType]) AND
> > ((Contracts.Status)<>[Forms]![MenuReports]![ContractStatus]) AND
> > (([Forms]![MenuReports]![ContractID]) Is Null) AND
> > (([Forms]![MenuReports]![ContractType]) Is Not Null) AND
> > (([Forms]![MenuReports]![ContractStatus]) Is Not Null)) OR
> > (((Contracts.ContractID)=[Forms]![MenuReports]![ContractID]) AND
> > ((Contracts.Status)<>[Forms]![MenuReports]![ContractStatus]) AND
> > (([Forms]![MenuReports]![ContractID]) Is Not Null) AND
> > (([Forms]![MenuReports]![ContractType]) Is Null) AND
> > (([Forms]![MenuReports]![ContractStatus]) Is Not Null)) OR
> > (((Contracts.Status)<>[Forms]![MenuReports]![ContractStatus]) AND
> > (([Forms]![MenuReports]![ContractID]) Is Null) AND
> > (([Forms]![MenuReports]![ContractType]) Is Null) AND
> > (([Forms]![MenuReports]![ContractStatus]) Is Not Null)) OR
> > (((Contracts.ContractID)=[Forms]![MenuReports]![ContractID]) AND
> > ((Contracts.ContractType)=[Forms]![MenuReports]![ContractType]) AND
> > (([Forms]![MenuReports]![ContractID]) Is Not Null) AND
> > (([Forms]![MenuReports]![ContractType]) Is Not Null) AND
> > (([Forms]![MenuReports]![ContractStatus]) Is Null)) OR
> > (((Contracts.ContractType)=[Forms]![MenuReports]![ContractType]) AND
> > (([Forms]![MenuReports]![ContractID]) Is Null) AND
> > (([Forms]![MenuReports]![ContractType]) Is Not Null) AND
> > (([Forms]![MenuReports]![ContractStatus]) Is Null)) OR
> > (((Contracts.ContractID)=[Forms]![MenuReports]![ContractID]) AND
> > (([Forms]![MenuReports]![ContractID]) Is Not Null) AND
> > (([Forms]![MenuReports]![ContractType]) Is Null) AND
> > (([Forms]![MenuReports]![ContractStatus]) Is Null)) OR
> > ((([Forms]![MenuReports]![ContractID]) Is Null) AND
> > (([Forms]![MenuReports]![ContractType]) Is Null) AND
> > (([Forms]![MenuReports]![ContractStatus]) Is Null));
> >
>
>
>

Relevant Pages

  • Re: MPEG playback time
    ... appear that the shorter video was advancing faster than the other. ... VirtualDub reports ... me is that the videos actually playback at different speeds in MPC. ... DirectShow filters to play the media. ...
    (rec.video.desktop)
  • RE: Report Filters
    ... want to do ad hoc filters by form and then run reports on the filtered sets. ... "Brook" wrote: ... > I am setting up a expense account database and want to create several reports ...
    (microsoft.public.access.reports)
  • staff schedule in excel
    ... excel. ... Employee Name ... no luck with filters or reports. ...
    (microsoft.public.excel.misc)
  • Re: Am I using too many filters?
    ... I try to create reports (record source queries) with no criterias. ... > criteria line to filter based on that control if it is populated. ... > do a join between them to apply ALL filters. ...
    (microsoft.public.access.queries)
  • Re: How do women read maps?
    ... or start to become media gurus (including writing down ... His reports tend to be spare to the point of terse, ... Oh, heck, I can do THAT. ...
    (uk.rec.walking)