Filter a Parent table from Child criteria

[mal54]

I need to filter a parent table from child criteria. but the function only supports aggregate expressions i.e. sum(field).

My solution is a database for company representatives each rep has a territory and I just want to be able to find the reps that operate in a particular territory. The tblInstructor is the parent and the tblTerritory is the child the relationship is tblInstructortblteritory

this is my code can you give me a work around to help.
Regards Mal


Private Sub FilterInstructorTerritory()

Dim strCriteria As String = ""

Dim cmInstructorCityID As CurrencyManager = CType(Me.BindingContext(DsInstructor1, "tblInstructor"), CurrencyManager)

Dim dvInstructorSearchByCityID As DataView = CType(cmInstructorCityID.List, DataView)

If Not (Me.cboFilterCity.SelectedValue = Nothing) Then

If strCriteria <> "" Then strCriteria = strCriteria & " AND "

strCriteria = strCriteria & "Min(Child(tblInstructortblTerritory).CityID) = " & cboFilterCity.SelectedValue & ""

End If

dvInstructorSearchByCityID.RowFilter = strCriteria

If dvInstructorSearchByCityID.Count > 0 Then

Me.lblCountFilterInstr.Text = "Filtered"

Else

'Dim Found As Boolean = False

'Dim MyCollection As New Collection

'For Each MyObject As Object In MyCollection ' Iterate through elements.

'If CStr(MyObject.Text) = "Hello" Then ' If Text equals "Hello"

'Found = True ' Set Found to True.

'Exit For ' Exit loop.

'End If

' Next

dvInstructorSearchByCityID.RowFilter = "FinishDate Is Null"

MsgBox("No service in location.", , "")

End If

Me.objdsInstructor_PositionChanged()

End Sub