set filter on subreport

[Bill H.]

How do I set a filter, and turn it on, on a subreport from a form?

I have a report that has one subreport which includes dates of attendance.
A form first ask for a start and end dates, and then runs the main report.
The query for the main report inclues the date field in question, but when I
try using the daterange as a filter on the main report ( DoCmd.OpenReport
stDocName, acPreview, , DateRange), I either get nothing or all (depending
on what I put in as the date range) of the records.

This approach:

Reports!Rpt_HomeVisitLog_subform.Filter = DateRange
Reports!Rpt_HomeVisitLog_subform.FilterOn = True

doesn't work as Access says the form is not open or doesn't exist (which it
does).


Thanks.

 

[Allen Browne]

As you found, trying to set the Filter of the subreport is an exercise in
frustration.

Instead, use a query as the RecordSource for your subreport, and put the
criteria in the query.

Typically you enter something like this in the Criteria row under the date
field in the query:
Between [Forms].[Form1].[txtStart] And [Forms].[Form1].[txtEnd]
This assumes you enter the dates onto Form1, into text boxes named txtStart
and txtEnd.

If the text boxes are unbound, set their Format property to Short Date so
Access understands the intended data type. It is also a good idea to declare
the 2 parameters in the query: choose Parameters on the Query menu, and
enter 2 rows in the dialog:
[Forms].[Form1].[txtStart] Date/Time
[Forms].[Form1].[txtEnd] Date/Time

 

[Bill H.]

Well, that worked pretty well. Thanks.

However, the selection fields need to be ANDED, and when I do that, I almost
always get a null result set unless all the form's fields are filled in.

What I'm needed is that only the filled in formfields are used in the query.
I have about 5 fields on the input form.

How to do that one?

--Bill

 

[Allen Browne]

Set up the WHERE clause of the query using this kind of thing:
WHERE (([Forms].[Form1].[Text0] Is Null)
OR ([SomeField] = [Forms].[Form1].[Text0]))
AND (([Forms].[Form1].[Text2] Is Null)
OR ([AnotherField = [Forms].[Form1].[Text2]))
AND ((...

Watch the bracketing. You need brackets around the OR, inside brackets
between the ANDs.

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

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

Well, that worked pretty well. Thanks.

However, the selection fields need to be ANDED, and when I do that, I
almost
always get a null result set unless all the form's fields are filled in.

What I'm needed is that only the filled in formfields are used in the
query.
I have about 5 fields on the input form.

How to do that one?

--Bill

As you found, trying to set the Filter of the subreport is an exercise in
frustration.

Instead, use a query as the RecordSource for your subreport, and put the
criteria in the query.

Typically you enter something like this in the Criteria row under the
date
field in the query:
Between [Forms].[Form1].[txtStart] And [Forms].[Form1].[txtEnd]
This assumes you enter the dates onto Form1, into text boxes named txtStart
and txtEnd.

If the text boxes are unbound, set their Format property to Short Date so
Access understands the intended data type. It is also a good idea to declare
the 2 parameters in the query: choose Parameters on the Query menu, and
enter 2 rows in the dialog:
[Forms].[Form1].[txtStart] Date/Time
[Forms].[Form1].[txtEnd] Date/Time

 

[Bill H.]

Thanks!

Set up the WHERE clause of the query using this kind of thing:
WHERE (([Forms].[Form1].[Text0] Is Null)
OR ([SomeField] = [Forms].[Form1].[Text0]))
AND (([Forms].[Form1].[Text2] Is Null)
OR ([AnotherField = [Forms].[Form1].[Text2]))
AND ((...

Watch the bracketing. You need brackets around the OR, inside brackets
between the ANDs.

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

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

"Bill H." wrote in message

Well, that worked pretty well. Thanks.

However, the selection fields need to be ANDED, and when I do that, I
almost
always get a null result set unless all the form's fields are filled in.

What I'm needed is that only the filled in formfields are used in the
query.
I have about 5 fields on the input form.

How to do that one?

--Bill

As you found, trying to set the Filter of the subreport is an exercise in
frustration.

Instead, use a query as the RecordSource for your subreport, and put the
criteria in the query.

Typically you enter something like this in the Criteria row under the
date
field in the query:
Between [Forms].[Form1].[txtStart] And [Forms].[Form1].[txtEnd]
This assumes you enter the dates onto Form1, into text boxes named txtStart
and txtEnd.

If the text boxes are unbound, set their Format property to Short Date so
Access understands the intended data type. It is also a good idea to declare
the 2 parameters in the query: choose Parameters on the Query menu, and
enter 2 rows in the dialog:
[Forms].[Form1].[txtStart] Date/Time
[Forms].[Form1].[txtEnd] Date/Time