Filter coding stopped working

[Freddie]

I set up a search form with 3 text boxes and 3 apply filter buttons and a
clear filter button. all these buttons worked, and i saved the database.
however, when i pulled the form back up, the buttons stopped working. I dont
think it is a coding error since it worked before, but here's the codes for
my buttons:

FOR ONE SEARCH
Dim strWhere As String
Dim lngLen As Long
If Not IsNull(Me.txtFilterSIC) Then
strWhere = strWhere & "([SIC] LIKE """ & Me.txtFilterSIC & "*"")"
Me.Filter = strWhere
Me.FilterOn = True
End If


FOR THE 2ND SEARCH BUTTON
Dim strWhere As String
Dim lngLen As Long
If Not IsNull(Me.txtFilterDesc) Then
strWhere = strWhere & "([Description] Like ""*" & Me.txtFilterDesc &
"*"")"
Me.Filter = strWhere
Me.FilterOn = True
End If


3rd search

Dim strWhere As String
Dim lngLen As Long
If Not IsNull(Me.txtFilterIndustry) Then
strWhere = strWhere & "([Industry] Like ""*" & Me.txtFilterIndustry
& "*"")"
Me.Filter = strWhere
Me.FilterOn = True
End If

Lastly, the clear filter button

On Error GoTo Err_Command51_Click

Dim stDocName As String

stDocName = "ClearFilter"
DoCmd.RunMacro stDocName
Me.txtFilterSIC = Null
Me.txtFilterDesc = Null
Me.txtFilterIndustry = Null

Exit_Command51_Click:
Exit Sub

Err_Command51_Click:
MsgBox Err.DESCRIPTION
Resume Exit_Command51_Click



These 4 codes are defined for 4 diff buttons, they just dont work anymore!
Help!

 

[Dale Fye]

Freddie,

Are any of the filters working? When I provided the example of the
filtering code yesterday, I assumed you were going to put all three textbox
filters in a single cmdFilter button. With them broken out the way you have
them, you can shorten the lines where you build the strWhere by dropping the
"strWhere &" right after the equals sign.

strWhere = "[Description] Like ....

I'm not sure why you are calling a macro in your ClearFilter code. I'd get
rid of the two lines:

stDocName = "clearFilter"
docmd.runmacro stDocName

and just replace it with the lines:

me.filter = ""
me.filteron = false

Dale

 

[Freddie]

My filter works only if I enter the first text box, and then one of the other
text boxes. It does not work if I simply enter something in the second text
box, or the second and 3rd box. Here's my code:

Private Sub Command34_Click()
Dim varCriteria As Variant

If Not IsNull(Me.txtbox1) Then
varCriteria = varCriteria & "([Field1] Like ""*" & Me.txtbox1 &
"*"") AND"

End If

If Not IsNull(Me.txtbox2) Then
varCriteria = varCriteria & "([Field2] Like ""*" & Me.txtbox2 &
"*"") AND"
End If

If Not IsNull(Me.txtbox3) Then
varCriteria = varCriteria & "([Field3] Like """ & Me.txtFilterSIC &
"*"")"

Me.Filter = varCriteria
Me.FilterOn = True

End If


End Sub

 

[Douglas J. Steele]

I suspect that's not your real code, and that you changed it when you posted
it, since that would only work if txtbox3 is not null (although it actually
uses the contens of txtFilterSIC, not txtbox3, in building the filter)

What you need is:

Private Sub Command34_Click()
Dim strCriteria As String

If Not IsNull(Me.txtbox1) Then
strCriteria = strCriteria & "([Field1] Like ""*" & _
Me.txtbox1 & "*"") AND "
End If

If Not IsNull(Me.txtbox2) Then
strCriteria = strCriteria & "([Field2] Like ""*" & _
Me.txtbox2 & "*"") AND "
End If

If Not IsNull(Me.txtFilterSIC) Then
strCriteria = strCriteria & "([Field3] Like """ & _
Me.txtFilterSIC & "*"") AND "
End If

If Len(strCriteria) > 0 Then
strCriteria = Left$(strCriteria, Len(strCriteria) - 5)
Me.Filter = strCriteria
Me.FilterOn = True
Else

Me.Filter = vbNullString
Me.FilterOn = False
End If

End Sub

 

[Dale Fye]

A couple of things wrong.
1. You were appending the " AND " at the end of a line, and there may never
have been any other criteria.
2. You were actually setting the filter inside the third If/EndIf statement
sequence.

Try it like this:

Private Sub Command34_Click()
Dim varCriteria As Variant

varCriteria = NULL
If Not IsNull(Me.txtbox1) Then
varCriteria = "([Field1] Like ""*" & Me.txtbox1 & "*"")"

End If

If Not IsNull(Me.txtbox2) Then
varCriteria = (varCriteria + " AND ") _
& "([Field2] Like ""*" & Me.txtbox2 & "*"")"
End If

If Not IsNull(Me.txtbox3) Then
varCriteria = (varCriteria + " AND ") _
& "([Field3] Like """ & Me.txtFilterSIC & "*"")"
End IF

Me.Filter = varCriteria
Me.FilterOn = True

End Sub

Dale