Possible to have single filter for a mix of blank and non blank criteria?

[Margarita]

Hello, I apologize if this topic was posted before- I couldn't find an answer to this particular question, though there are lots of threads about multiple criteria.

Is it possible to have a SINGLE filter button that filters a form only by the filter criteria that the user specifies and not all the possible criteria that the filter may have? That is, if there are multiple criteria that the user can filter by but she only chooses to specify, say, two out of five, and leaves the other three filter boxes blank, I want the filter to only pick up the ones she specified and NOT filter by Nulls in the other three fields.

I have 5 fields on which the user can filter by typing in what they want in text boxes. I don't want to have 5 separate filter buttons, I want just one that sets the filter on all the specified criteria. Is it possible for the button to pick out only the criteria that the user specified? I mean, without having to go through ten thousand if statements like

If len(me.filterfield1)>0 and len(me.filterfield2)= 0 and... len(me.filterfield5)= 0 then
'set filter string to just the first criteria

Else

...and so on.

Or is it better to just have a separate filter button for each text box?

Thank you.

 

[boblarson]

Just do it like this:

Dim strWhere As String
 
If Len(Me.FilterField1 & vbNullString) > 0 Then
   strWhere = "[FieldNameHere] = " & Me.FilterField1 & " AND "
End If
 
If Len(Me.FilterField2 & vbNullString) > 0 Then
   strWhere = strWhere & "[Field2NameHere]=" & Me.FilterField2 & " AND "
End If
 
If Len(Me.FilterField3 & vbNullString) > 0 Then
   strWhere = strWhere & "[Field3NameHere]=" & Me.FilterField3 & " AND "
End If
 
If Len(Me.FilterField4 & vbNullString) > 0 Then
   strWhere = strWhere & "[Field4NameHere]=" & Me.FilterField4 & " AND "
End If
 
If Len(Me.FilterField5 & vbNullString) > 0 Then
   strWhere = strWhere & "[Field5NameHere]=" & Me.FilterField5 & " AND "
End If
 
If Right(strWhere, 5) = " AND " Then
   strWhere = Left(strWhere, Len(strWhere) - 5)
End If

Then you use strWhere like in the

Me.Filter = strWhere
Me.FiterOn = True

or if opening another form

DoCmd.OpenForm "FormNameHere", acNormal, , strWhere

Also, if you have a date field instead of number use:

If Len(Me.FilterField2 & vbNullString) > 0 Then
   strWhere = strWhere & "[Field2NameHere]=[B][COLOR=red]#[/COLOR][/B]" & Me.FilterField2 & "[B][COLOR=red]#[/COLOR][/B] AND "
End If

And if it is TEXT then it would be:

If Len(Me.FilterField2 & vbNullString) > 0 Then
   strWhere = strWhere [B][COLOR=red]& Chr(34)[/COLOR][/B] & "[Field2NameHere]=" & Me.FilterField2 & [B][COLOR=red]Chr(34) &[/COLOR][/B]  " AND "
End If

 

[Margarita]

Wow! That's a really awesome way to do it! Thank you!
I just tried it and the form does get filered. HOWEVER, something truly bizarre is happening:

no matter what purchase order, year, budget code, etc I specify, the form gets filtered by one and only one purchase order number- the same one every time. For example, I am typing in "Subcontract" into DocTypetoSearch and it's still filtering the form by that one purchase number, which has a DocType of PO, not Subcontract.

This is exactly what I have for the filter on and filter off buttons:

Private Sub FilterButton_Click()
Dim strWhere As String
 
If Len(Me.FYtoSearch & vbNullString) > 0 Then
   strWhere = "[FiscalYear] = " & Chr(34) & Me.FYtoSearch & Chr(34) & " AND "
End If
 
If Len(Me.DocTypetoSearch & vbNullString) > 0 Then
   strWhere = strWhere & Chr(34) & "[DocumentType]=" & Me.DocTypetoSearch & Chr(34) & " AND "
End If
 
If Len(Me.DocNumtoSearch & vbNullString) > 0 Then
   strWhere = strWhere & Chr(34) & "[DocumentNumber]=" & Me.DocNumtoSearch & Chr(34) & " AND "
End If
 
If Len(Me.OCtoSearch & vbNullString) > 0 Then
   strWhere = strWhere & Chr(34) & "[ObjectCode]=" & Me.OCtoSearch & Chr(34) & " AND "
End If
 
If Len(Me.BudgetCodetoSearch & vbNullString) > 0 Then
   strWhere = strWhere & Chr(34) & "[BudgetCode]=" & Me.BudgetCodetoSearch & Chr(34) & " AND "
End If
 
If Right(strWhere, 5) = " AND " Then
   strWhere = Left(strWhere, Len(strWhere) - 5)
End If
Me.Filter = strWhere
Me.FilterOn = True
End Sub
Private Sub FilterOffButton_Click()
Me.FilterOn = False
Me.Filter = vbNullString
End Sub