Applying a Filter to a Sub Report

[Eka1618]

Hello,

I am trying to generate a report based on the following query: qryTestReports

The SQL for this query is:
SELECT tblRequest.REQUEST_NO, tblRequest.TITLE, tblRequest.NOTES,
tblQueue.Q_INC, tblQueue.QID, tblQueue.Q_YEAR, tblTest.TEST_TYPE,
tblTest.A_M, tblTest.WRENCH_NO, tblTest.INSTALL_TRQ, tblTest.DESCRIPTION,
tblTest.MIN_TORQ, tblTest.BOTH_DIRECTIONS, tblKey.K_ID, tblKey.K_PART_NO,
tblKey.K_EWO_NO, tblKey.K_SKID_NO, tblLock.L_ID, tblLock.LOCK_LUG,
tblLock.LUG_TOOL, tblLock.L_PART_NO, tblLock.L_EWO_NO, tblLock.L_SKID_NO
FROM (((tblRequest INNER JOIN tblTest ON tblRequest.REQUEST_NO =
tblTest.REQUEST_NO) INNER JOIN tblQueue ON tblTest.TEST_ID =
tblQueue.TEST_ID) INNER JOIN tblKey ON tblRequest.REQUEST_NO =
tblKey.REQUEST_NO) INNER JOIN tblLock ON tblRequest.REQUEST_NO =
tblLock.REQUEST_NO;


This Report has a subreport and in the sub report I need it to filter on two
different conditions. If the parent report's K_ID field is Null, then I want
my sub report to filter like this:

[L_ID]=Reports.[rptOffsetTestReport].L_ID

If the parent report's K_ID field is not null then I want my sub report to
filter like this:

[L_ID]=Reports.[rptOffsetTestReport].L_ID and [K_ID] =
Reports.[rptOffsetTestReport].[K_ID]

I cannot seem to figure out the point at which the report is filtered. I
have tried using the following code on onOpen, onLoad, onFilter:

If Me.Parent!K_ID.Value Is Null Then
Me.Filter = "[L_ID] = Reports.[rptStaticTestReport].[L_ID]"
Else
Me.Filter = "[L_ID] = Reports.[rptStaticTestReport].[L_ID]" And
"[K_ID] = Reports.[rptStaticTestReport].[K_ID]"
End If

I'm not that great with reports yet, so any help would be great. Thank you!

 

[Dale Fye]

Erica,

First, I would not use the Filter in a report. Get rid of that code
altogether.

I would actually write the query of the sub-report so that it filters
properly. The WHERE clause would of the sub-report would look something like:

WHERE Reports!rptStaticTestReport.K_ID IS NULL OR
(Reports!rptStaticTestReport.K_ID IS NOT NULL AND
[K_ID] = Reports!rptStaticTestReport.K_ID)

Then, I would set the Master/Child fields of the main and subform to [L_ID]

I tested this, and for those records in the parent table where [K_ID] was
null, it listed all of the values in the sub report, but where [K_ID] had
data, it only matched those records where both L_ID and K_ID matched.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Eka1618]

Hi Dale,

This method does work. I actually figured it out prior to reading your
response and this is the method I was using:

1. In the query I used: WHERE (((tblResults.L_ID) Like
[Reports].[rptImpactTestReport].[L_ID]));

2. In the filter property of the subReport I used: [K_ID] =
Reports.[rptImpactTestReport].K_ID or [K_ID] =
Reports.[rptImpactTestReport].K_ID is null

3. The Master/Child Link was: Q_INC

This did the same thing that your way did. I have multiple forms that will
all use the same query, so I didn't want to have to write a query for each
report. Is the method that I use bad? If so, then I will create a query for
each report. I am just curious why you say not to filter the report at all.

If you know of a way I can still use one query for each report while using
your method please let me know.

Thank you for your help!
--
~Erica~

Erica,

First, I would not use the Filter in a report. Get rid of that code
altogether.

I would actually write the query of the sub-report so that it filters
properly. The WHERE clause would of the sub-report would look something like:

WHERE Reports!rptStaticTestReport.K_ID IS NULL OR
(Reports!rptStaticTestReport.K_ID IS NOT NULL AND
[K_ID] = Reports!rptStaticTestReport.K_ID)

Then, I would set the Master/Child fields of the main and subform to [L_ID]

I tested this, and for those records in the parent table where [K_ID] was
null, it listed all of the values in the sub report, but where [K_ID] had
data, it only matched those records where both L_ID and K_ID matched.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Hello,

I am trying to generate a report based on the following query: qryTestReports

The SQL for this query is:
SELECT tblRequest.REQUEST_NO, tblRequest.TITLE, tblRequest.NOTES,
tblQueue.Q_INC, tblQueue.QID, tblQueue.Q_YEAR, tblTest.TEST_TYPE,
tblTest.A_M, tblTest.WRENCH_NO, tblTest.INSTALL_TRQ, tblTest.DESCRIPTION,
tblTest.MIN_TORQ, tblTest.BOTH_DIRECTIONS, tblKey.K_ID, tblKey.K_PART_NO,
tblKey.K_EWO_NO, tblKey.K_SKID_NO, tblLock.L_ID, tblLock.LOCK_LUG,
tblLock.LUG_TOOL, tblLock.L_PART_NO, tblLock.L_EWO_NO, tblLock.L_SKID_NO
FROM (((tblRequest INNER JOIN tblTest ON tblRequest.REQUEST_NO =
tblTest.REQUEST_NO) INNER JOIN tblQueue ON tblTest.TEST_ID =
tblQueue.TEST_ID) INNER JOIN tblKey ON tblRequest.REQUEST_NO =
tblKey.REQUEST_NO) INNER JOIN tblLock ON tblRequest.REQUEST_NO =
tblLock.REQUEST_NO;


This Report has a subreport and in the sub report I need it to filter on two
different conditions. If the parent report's K_ID field is Null, then I want
my sub report to filter like this:

[L_ID]=Reports.[rptOffsetTestReport].L_ID

If the parent report's K_ID field is not null then I want my sub report to
filter like this:

[L_ID]=Reports.[rptOffsetTestReport].L_ID and [K_ID] =
Reports.[rptOffsetTestReport].[K_ID]

I cannot seem to figure out the point at which the report is filtered. I
have tried using the following code on onOpen, onLoad, onFilter:

If Me.Parent!K_ID.Value Is Null Then
Me.Filter = "[L_ID] = Reports.[rptStaticTestReport].[L_ID]"
Else
Me.Filter = "[L_ID] = Reports.[rptStaticTestReport].[L_ID]" And
"[K_ID] = Reports.[rptStaticTestReport].[K_ID]"
End If

I'm not that great with reports yet, so any help would be great. Thank you!