Coding for a Filter to look at a separate table

[Tim]

I have a report working from a fairly involved query, and then I have
the launch of the report build a filter string based on selections
made off of a form.
The unique piece of data for each record in the query is called
Project_ID.

I would like to add to the FILTER string for this report using code,
something that looks at TableX which also has a Project_ID field
(TableX is not part of the query datasource) as follows:
Look in TableX for any records that have the same Project_ID as the
query datasource for the report, and of those, only where
TableX.FieldX = 1 or 2.

I want to use this to filter my datasource, but only if the user
selects to apply this filter to the report.

 

[Allen Browne]

Use a subquery in the WhereCondition of your OpenReport action.
It will end up something like this:

Dim strWhere As String
Dim lngLen As Long

If Me.FilterOn Then
strWhere = strFilter & " AND "
End If

If Not IsNull(Me.txtYourFilterBoxForFieldX)
strWhere = strWhere & "EXISTS (SELECT FieldX FROM TableX WHERE
(TableX.Project_ID = MainTable.Project_ID) AND (TableX.FieldX = " &
Me.txtYourFilterBoxForFieldX & ")) AND "
End If

'other filter conditions you wish to add here.

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
End If

DoCmd.OpenReport "ReportX", acViewPreview, , strWhere


If subqueries are a new idea, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066