Filtering

  • Thread starter Thread starter JayTee
  • Start date Start date
J

JayTee

Hi all,

I am trying to filter data with the following code:



Dim strFilter As String

Dim lngLen As Long



If Me.Dirty Then

Me.Dirty = False

End If



If Not IsNull(Me.txtAgency) Then

strFilter = strFilter & "([Agency] = """ & Me.txtAgency & """) And "

End If



If Not IsNull(Me.txtState) Then

strFilter = strFilter & "([State] = """ & Me.txtState & """) And "

End If



If Not IsNull(Me.txtSurname) Then

strFilter = strFilter & "([Surname] = """ & Me.txtSurname & """) And "

End If



lngLen = Len(strFilter) - 5

If lngLen < 1 Then

MsgBox "Enter a criteria first"

Else

Me.Filter = Left(strFilter, lngLen)

Me.FilterOn = True

End If



This works well but I would like to still be able to filter if I should
leave one or more of these search fields blank.

The above code will not allow blank search text boxes. How do I change to
code so I can leave one or more text boxes blank.

Thanks in advance to anyone that can help me.
 
This works well but I would like to still be able to filter if I should
leave one or more of these search fields blank.

The above code will not allow blank search text boxes. How do I change to
code so I can leave one or more text boxes blank.

Thanks in advance to anyone that can help me.
Click to expand...

Hi

Although code below is based on strWhere instead of strFilter II think
you should be able to convert ir easily, if not, let me know.

'check company name
If Not IsNull(Me.txtCompanyName) And Not Me.txtCompanyName = "" Then
If strWhere = "" Then
strWhere = "CompanyName = """ & txtCompanyName & """"
Else
strWhere = strWhere & " AND CompanyName = """ & txtCompanyName &
""""
End If
End If

'check job title
If Not IsNull(Me.txtJobTitle) And Not Me.txtJobTitle = "" Then
If strWhere = "" Then
strWhere = "JobTitle = """ & txtJobTitle & """"
Else
strWhere = strWhere & " AND JobTitle = """ & txtJobTitle & """"
End If
End If

'check job number
If Not IsNull(Me.txtJobNumber) Then
If strWhere = "" Then
strWhere = "JobNumber = """ & txtJobNumber & """"
Else
strWhere = strWhere & " AND JobNumber = " & txtJobNumber
End If
End If
 
Back
Top