Filtering forms with mutliple conditions

[sal]

I have been working away on a form designed to serve up many combinations of data depending on what the form controls are set to. I have three combo boxes, each of which limits records when populated and returns all when NULL.

I have three radio buttons which set the values for three text boxes depending on whether they are checked or not, and I have folded code into my filter to return all values if they are not checked.

But unlike the other filters where I want each to successively limit the records, I want my radio buttons, denoted in BOLD by the text boxes in the code below to return all that are True or not NULL, yet return all if none are checked.

Right now everything works great if I check one, but when I check a second all records are filtered out and none are returned.

This is my code:

Private Sub RunFilter3()

Dim strFilter As String
Dim bFilter As Boolean

bFilter = False
strFilter = ""

If Nz(Me.ComboRunYear, 0) > 0 Then 'Period
If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
strFilter = strFilter & "RunYear = " & Me.ComboRunYear
bFilter = True
End If

If Nz(Me.ComboWater, 0) > 0 Then 'TargetType
If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
strFilter = strFilter & "StreamName = '" & Me.ComboWater & "'"
bFilter = True
End If

If Nz(Me.ComboAgency, 0) > 0 Then 'TargetType
If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
strFilter = strFilter & "AgencyName = '" & Me.ComboAgency & "'"
bFilter = True
End If

If Nz(Me.STHDValue, 0) > 0 Then 'TargetType
If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
strFilter = strFilter & "Species_AbbrDesc = '" & Me.STHDValue & "'"
bFilter = True
End If

If Nz(Me.CUTTValue, 0) > 0 Then 'TargetType
If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
strFilter = strFilter & "Species_AbbrDesc = '" & Me.CUTTValue & "'"
bFilter = True
End If

If Nz(Me.COHOValue, 0) > 0 Then 'TargetType
If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
strFilter = strFilter & "Species_AbbrDesc = '" & Me.COHOValue & "'"
bFilter = True
End If

If bFilter Then
Me.STHD_Project_Data_Export_SF.Form.OrderBy = ""
Me.STHD_Project_Data_Export_SF.Form.Filter = strFilter
Me.STHD_Project_Data_Export_SF.Form.FilterOn = True
Else
Me.STHD_Project_Data_Export_SF.Form.FilterOn = False
End If

End Sub

Thank you for taking the time to check this out.

 

[MarkK]

Do you need to use OR instead of AND in your filter expression, something like...

Test1 AND Test2 AND ( Radio1 [B]OR[/B] Radio2 [B]OR[/B] Radio3 ) AND SoOn

 

[sal]

Yes, that is where I need help. I need to figure out how to combine the last three If Then statements into one with three OR's. I will experiment with it. I am certain it can be done but I just don't have the programming expertise to do it...

 

[sal]

To help me explain my filter, I have attached a screen shot of the form (see attached image).

On the upper left of the form there are three Combo Boxes, each limits the data in the sub form by its respective value but Null values are ignored.

On the upper right are the three Radio Boxes which set the values in each of the text boxes (these will be hidden when working) next to them. I want to use these to return all "species" when none are selected (all text boxes are null. But once selections are made for one or more I want only those records that satisfy the Combo box criteria AND all of the species values in the text boxes.

Right now the filter works for one species checked but when I check a second species the filter looks for a subset of the data which has already excluded the second species, thus returning an empty record set.

I tried putting a Forms![FormName]![Text box1] OR Forms![FormName]![Text box2] OR Forms![FormName]![Text box3]

Expression directly into the query but so far that approach is also return an empty record set.

Thanks again for your patience.