Re: Am I using too many filters?

From: Duane Hookom (duanehookom_at_NO_SPAMhotmail.com)
Date: 02/15/05 

Date: Mon, 14 Feb 2005 21:46:24 -0600

I try to create reports (record source queries) with no criterias. Instead,
I use code in the form "MenuReports" to create a where string that is used
in the DoCmd.OpenReport method. For instance:

    Dim strWhere as String
    strWhere = "1=1 "
    If Not IsNull(Me.ContractID) Then
        'assuming ContractID is numeric
        strWhere = strWhere & " And ContractID = " & Me.ContractID
    End If
    If Not IsNull(Me.ContractType) Then
        'assuming ContractType is text
        strWhere = strWhere & " And ContractType = """ & Me.ContractType &
""" "
    End If
    '--- etc -----
    'you can add code that loops through a multi-select list box
    'this makes date ranges easy
    'the only major issue is you can't apply this technique very easily
    ' to subreports
    DoCmd.OpenReport "rptYourReport", acPreview, , strWhere 

-- 
Duane Hookom
MS Access MVP
"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: Same Parameters in all sub reports
    ... "Jeff Boyce" wrote: ... You design queries that return the records you want. ... I actually can use the same criteria for 2 separate reports. ...
    (microsoft.public.access.reports)
  • Re: Can a Form Set Up Criteria in a Query
    ... you might type something this into the Criteria row of your ... Where are are not stacking one query on another, you may be able to just the ... >I have a database of salary survey information that I use to produce> about twenty customised reports, based on about sixty separate queries. ...
    (microsoft.public.access.forms)
  • Re: A2007 ADPs
    ... Passthrough queries can be good for complex reports but excerpt for the ... I admit I had forgotten about this feature, not having done much MDB work ... When the schema of the database is not too complexe, ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Convert MDB to ADP
    ... I will try to explain what I'm using in access mdb about filter queries ... I have a form where I generate reports, queries, forms and by automation I ... Is it possibel to find any function in SQL server to replace Format, ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Linking in Access
    ... Another approach to consider would be to create the front-end, ... queries, forms, reports, then convert it to an .MDE file. ... maybe in a diff DB (diff forms for diff ...
    (microsoft.public.access.gettingstarted)