Re: filtering reports

From: Allen Browne (AllenBrowne_at_SeeSig.Invalid)
Date: 08/26/04 

Date: Thu, 26 Aug 2004 11:19:36 +0800

Filtering subreports is always fun.

In the simplest case, the LinkMasterFields/LinkChildFields automatically
limit the subreport so it contains the right records. Nothing else needed.
There are lots of cases where that's not enough.

The next level is to use a form as you suggest, and in the query that feeds
the subreport, refer to the controls on the form in the Criteria under the
various fields, e.g.:
    [Forms]![TheTorm]![DateOfService]
However, where these are optional the WHERE clause of the query starts to
get really messy and inefficient.

The next level is to dynamically create the SQL string in VBA, using only
those controls on the form that have a value. Then write this to the SQL
property of the subreport's QueryDef before opening the main report. This
kind of thing:
    Dim strSql As String
    Dim lngLen As Long
    Const strcStub = "SELECT * FROM MySubReportTable "
    Const strcTail = " ORDER BY [Last Name];"

    If not IsNull(Me.DateOfService) Then
        strSql = strSql & "([Date Of Service] = " & _
        Format(Me.DateOfService, "\#mm\/dd\/yyyy\#") & ") AND "
    End If

    If Not IsNull(Me.LastName) Then
        strSQL= strSql & "([Last Name] = """ & Me.LastName & """) AND "
    End If

    'etc for other controls.

    lngLen = Len(strSQL) - 5 'without trailing " AND ".
    If lngLen >= 0 Then
        strSQL = "WHERE (" & Left$(strSql, lngLen) & ")"
    End If
    strSql = strcStub & strSql & strcTail

    dbEngine(0)(0).QueryDefs("MySubreportQuery").SQL = strSql
    DoCmd.OpenReport "MyReport", acViewPreview

In Access 2002 and 2003, you can build up a description string at the same
time as you are building up the Where clause, pass it to the main report in
the OpenArgs of the OpenReport, and display in on the header of the report. 

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Mark R" <anonymous@discussions.microsoft.com> wrote in message
news:041301c48b10$ed381630$a501280a@phx.gbl...
> my report has a main source table and several sub reports
> with their oown source tables. another similar report has
> a query as its source and the query pulls from several
> tables
>
> I want the user to be able to run the report filtering on
> any combination of several fields from the parent main
> source table.
>
> 1. date of service
> 2. last name
> 3. first name
> 4. type of service
> 5. id
>
> 1 and 4
> 1, 2 and 4
> etc
>
> what would you suggest?
>
> I thought I could have a table with one record with these
> 5 fields and a form with these fields on it, after the
> user fills out which fields to filter on, visible-false,
> hide it, and then
>
> for the report based on the tables, put on the filter
> property line
> [date of service] = forms!theform!dateofservice
> and
> [last name] = forms!theform!lastname]
> and
> etc
>
> and likewise in the query use similar bracketing in the
> criteria on design view
>
> but these all do not seem to work.

Relevant Pages