Nz on a filter

[dominicgingras]

I have two combo box on a form that I use to filter data.

I use a button with the following code:

DoCmd.ApplyFilter "", "[field1] = '" & Me.Cmbox1 & "' and [field2]= '" & Me.Cmbox2 & "'"

How can I return a all value(*) if the combo box is empty

I could use a combination of Nz(xxx,like"**) but so far I had no success.

Any Idea?

 

[VBslammer]

Private Sub cmdFilter_Click()
  Dim strFilter As String
  
  If Len(Me.Combo1) Then
    strFilter = "[Field1]='" & Me.Combo1 & "'"
  End If
  If Len(Me.Combo2) Then
    If Len(strFilter) Then
      strFilter = strFilter & " And "
    End If
    strFilter = strFilter & "[Field2]='" & Me.Combo2 & "'"
  End If
  
  If Len(strFilter) Then
    DoCmd.ApplyFilter , strFilter
  End If
  
End Sub

VBSlammer

Unemployed in Houston, Texas

 

[dominicgingras]

What does the len do exactly. Usualy its for counting caracters???

 

[VBslammer]

Len(string) returns 0 for empty strings which is equivalent to False. VBSlammer

Unemployed in Houston, Texas

 

[dominicgingras]

But what I am trying to do is to return all value instead of 0.

This is a product form with a filter for categories. When the filter is empty, I want to display all category not 0 category.

I am sorry if my question was not clear!!

 

[VBslammer]

Just remove the filter to return all records. This code block will check your filter length, and if it is empty (0 length) it turns the filter off, otherwise it applies the filter.

'...continued...

If Len(strFilter) Then
  DoCmd.ApplyFilter , strFilter
Else
  Me.FilterOn = False
End If

VBSlammer

Unemployed in Houston, Texas