filtering reports

[Mark R]

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.

 

[Allen Browne]

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.

Reply to group, rather than allenbrowne at mvps dot org.

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.

 

[Mark r]

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.

Reply to group, rather than allenbrowne at mvps dot org.

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.

.

 

[Allen Browne]

Not sure I followed that.

To filter the *main* report, you can use the WhereCondition argument of
OpenReport. It's very simple, and requires no messing with the source
queries, or RecordSource or even the Filter property. See the example at:
http://members.iinet.net.au/~allenbrowne/casu-08.html

If you need to combine two conditions, mock up a query with entries under
two fields in the Criteria row. Then switch this query to SQL View (View
menu), and look at the WHERE clause. That's exactly the kind of string you
need to create. It will look something like this:
"(Field1 = 99) AND (Field2 = 999)"

Re Q2, the simplest way that works is the best.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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.

Reply to group, rather than allenbrowne at mvps dot org.

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.