Filter subreport

[Salvador1273]

Hello,
I'm trying to filter a subreport from a form but is not working could
somebody has an idea? Here it is the case: I have a calendar report which is
the Main report then I have a subreport that contains the information. On my
form criteria I'm trying to use two criterias: one is the date range which is
working fine; the second criteria is a list box with multiple options so
depending of user selection I want to pass the filter to the subreport, here
it is when I have the problem. I'm using a query as recordsource for the
subreport. I also checked the Allen Browne tips.

Thank you

 

[Allen Browne]

It's probably best to have the subreport's query read the limiting dates
from a form.

With text boxes, you would enter Criteria in your query like this:
Between [Forms].[Form1].[txtStartDate] And [Forms].[Form1].[txtEndDate]
I'm not clear how your list box works.

If that approach is not possible, you might be able to patch your filter in
as the WHERE clause of the query before you OpenReport. This kind of thing:

Dim strWhere As String
Dim strSql As String
Const strcStub = "SELECT * FROM Table1 WHERE ("
Const strcTail = ") ORDER BY Field1;"

strWhere = "[Field1] >= #1/1/2008#"
strSql = strcStub & strWhere & strcTail
CurrentDb.QueryDefs("YourSubreportQueryNameHere").SQL = strSql
DoCmd.OpenReport "Report1", acViewPreview

 

[Allen Browne]

If you want to use a multi-select list box to filter a subreport, the
options that come to mind are:

a) Assign the SQL property of the subreport's QueryDef before you
OpenReport. That's what I suggested last reply.

b) Write a VBA function that can be used in the query. It takes 2 arguments:
the field from the query, and the list box. It returns True if there is a
match, or False if there is not. Use the function in the WHERE clause of the
query.

c) Redesign the report so it doesn't need a subreport. You can then filter
it directly.

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

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

Allen,

For limiting dates I have already a form and is working really good. At
the
same form I also have a list box for the user to select values as needed,
this item will be another filter for the subreport. Something like this:
The
list box shows estimate status (Budget, In Estimating, Lost, etc), so the
user can select 1, 2 or more. The form also has a button to open the main
report, on that button I'm using one of your tips to validate how many
items
from the list box are selected, and then open the report. After I hit the
button a window shows up asking for the Status field (is because I want to
pass the filter to the subreport and not to the main report). So I don't
know
how can I approach this part.

I also have another report using list box as filter and that one is
working
perfect and the reason is because the filter applies to the Main report
(I'm
using your tip... thank you)

I hope this clarify my problem ..... thank you

It's probably best to have the subreport's query read the limiting dates
from a form.

With text boxes, you would enter Criteria in your query like this:
Between [Forms].[Form1].[txtStartDate] And
[Forms].[Form1].[txtEndDate]
I'm not clear how your list box works.

If that approach is not possible, you might be able to patch your filter
in
as the WHERE clause of the query before you OpenReport. This kind of
thing:

Dim strWhere As String
Dim strSql As String
Const strcStub = "SELECT * FROM Table1 WHERE ("
Const strcTail = ") ORDER BY Field1;"

strWhere = "[Field1] >= #1/1/2008#"
strSql = strcStub & strWhere & strcTail
CurrentDb.QueryDefs("YourSubreportQueryNameHere").SQL = strSql
DoCmd.OpenReport "Report1", acViewPreview

Hello,
I'm trying to filter a subreport from a form but is not working could
somebody has an idea? Here it is the case: I have a calendar report
which
is
the Main report then I have a subreport that contains the information.
On
my
form criteria I'm trying to use two criterias: one is the date range
which
is
working fine; the second criteria is a list box with multiple options
so
depending of user selection I want to pass the filter to the subreport,
here
it is when I have the problem. I'm using a query as recordsource for
the
subreport. I also checked the Allen Browne tips.

 

[Salvador1273]

Allen,

Option a) works perfect. Thank you very much.

If you want to use a multi-select list box to filter a subreport, the
options that come to mind are:

a) Assign the SQL property of the subreport's QueryDef before you
OpenReport. That's what I suggested last reply.

b) Write a VBA function that can be used in the query. It takes 2 arguments:
the field from the query, and the list box. It returns True if there is a
match, or False if there is not. Use the function in the WHERE clause of the
query.

c) Redesign the report so it doesn't need a subreport. You can then filter
it directly.

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

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

Allen,

For limiting dates I have already a form and is working really good. At
the
same form I also have a list box for the user to select values as needed,
this item will be another filter for the subreport. Something like this:
The
list box shows estimate status (Budget, In Estimating, Lost, etc), so the
user can select 1, 2 or more. The form also has a button to open the main
report, on that button I'm using one of your tips to validate how many
items
from the list box are selected, and then open the report. After I hit the
button a window shows up asking for the Status field (is because I want to
pass the filter to the subreport and not to the main report). So I don't
know
how can I approach this part.

I also have another report using list box as filter and that one is
working
perfect and the reason is because the filter applies to the Main report
(I'm
using your tip... thank you)

I hope this clarify my problem ..... thank you

It's probably best to have the subreport's query read the limiting dates
from a form.

With text boxes, you would enter Criteria in your query like this:
Between [Forms].[Form1].[txtStartDate] And
[Forms].[Form1].[txtEndDate]
I'm not clear how your list box works.

If that approach is not possible, you might be able to patch your filter
in
as the WHERE clause of the query before you OpenReport. This kind of
thing:

Dim strWhere As String
Dim strSql As String
Const strcStub = "SELECT * FROM Table1 WHERE ("
Const strcTail = ") ORDER BY Field1;"

strWhere = "[Field1] >= #1/1/2008#"
strSql = strcStub & strWhere & strcTail
CurrentDb.QueryDefs("YourSubreportQueryNameHere").SQL = strSql
DoCmd.OpenReport "Report1", acViewPreview

Hello,
I'm trying to filter a subreport from a form but is not working could
somebody has an idea? Here it is the case: I have a calendar report
which
is
the Main report then I have a subreport that contains the information.
On
my
form criteria I'm trying to use two criterias: one is the date range
which
is
working fine; the second criteria is a list box with multiple options
so
depending of user selection I want to pass the filter to the subreport,
here
it is when I have the problem. I'm using a query as recordsource for
the
subreport. I also checked the Allen Browne tips.