Re: filtering reports

From: Mark r (anonymous_at_discussions.microsoft.com)
Date: 08/26/04 

Date: Thu, 26 Aug 2004 04:12:52 -0700

Wow,

Thanks. I will try to work that out...........especially
for the report driven by the query.

I have two questions at the bottom of this
preface...please get to them

PREFACE:

But for the very very lengthy and intricate report that I
created that does NOT use a query as a source but rather
the table firectly, it sounds like I am stuck.

option 1: try to have the main report part feed from a
query and let the subreports feed off their tables
directly.

option 2:

have a command button labeled
REPORT BY DATE OF SERVICE:
>> put on the filter property line
>> [date of service] = forms!theform!dateofservice

a second command button labeled
REPORT BY DATE OF SERVICE AND LAST NAME:
>> put on the filter property line
>> [date of service] = forms!theform!dateofservice
>> and
>> [last name] = forms!theform!lastname]

a third button labeld
REPORT BY DATE OF SERVICE AND LAST NAME and FIRST ANME:
etc

QUESTIONS:
1. CAN I put multiple filters on the property line and how.
2. how disappointed in such cop-out code for a beginner
lieke me would you be?

>-----Original Message-----
>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.
>
>
>.
>