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.

 

[Mark R]

I put in the code for the query supplied report
I am getting error message 3296
JOIN EXPRESSION NOT SUPPORTED
HELP: ON statement of join contains too many tables

cnst strcstub2 = "from table1 inner join table2 on
table1.id = table2.id"

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

.

 

[Allen Browne]

Does this relate to the idea of the Filter (last reply) or assigning the
RecordSource (previous reply)?

If it is a complete SQL statement, make sure you have the SELECT clause, and
make sure the ID field is the same data type (and size) in both tables.

If it is a filter, drop the FROM clause, or use a subquery with a WHERE
clause.

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

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

I put in the code for the query supplied report
I am getting error message 3296
JOIN EXPRESSION NOT SUPPORTED
HELP: ON statement of join contains too many tables

cnst strcstub2 = "from table1 inner join table2 on
table1.id = table2.id"

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

message
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]

The problem is deeper than that

SELECT clause

got one

ID field is the same data type

absolutely same

I used
const strcstub = SELECT table1.fld1-6 table2.fld1-6
cnst strcstub2 = "from table1 inner join table2
on table1.id = table2.id"

I am getting error message 3296
JOIN EXPRESSION NOT SUPPORTED
HELP: ON statement of join contains too many tables

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.

message
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]

Mark, mock up a query that uses these 2 tables, and produces the results you
want. Then switch to SQL View (View menu in query deisgn), and imitate what
you see.

 

[Mark r]

Allen,

That is what I did.
This is what was in SQL view.

cnst strcstub2 = "from table1 inner join table2
on table1.id = table2.id"

I know you are trying to help and you have been quite
helpful on other issues. But since you are obviously
either stumped or not comprehending the entire issue here,
I am going to post a new threa for someone else to try to
contribute.