Filter

  • Thread starter Thread starter Zenon
  • Start date Start date
Z

Zenon

I am having trouble with a simple filter, hope someone can help. I
have a form based on a query. I am trying to filter the form's output
by the text of a textbox. The event is a command button. Here is my
attempts at the code, none of which worked

Private Sub btn_Filter_Description_Click()

Me.Description.SetFocus
If (IsNull(Me.Description) Or Me.Description.Text = "") Then
MsgBox ("Cannot filter by blank value")
Else
Dim stmt As String
Dim arg As String
Me.Description.SetFocus
'stmt = Me.Description.Text
arg = Forms![form_Incoming].Description.Text

stmt = BuildCriteria("Description", dbText, arg)
Me.Filter = stmt
'Me.Description.SetFocus
'Me.Form.Filter = "Description.Text = " & stmt
'Me.Form.FilterOn = True

End If

End Sub




thanks,

Zenon
 
Zenon,

I build a "filter" in a similar manor but I just use the record set.

I have text boxes on my from called SLast, SFirst, SPhone, and SEmpID

Then two CMD buttons, one to search (cmdSearch_Click) and one to reset to
the original state.

In my code if both first and last are filled in it will use both of them.

******** Code Start ************************

Private Sub cmdSearch_Click()
Dim strSQL As String
'If both First (SFirst) and Last (SLast) name have data entered then
restrict using both.
If Not IsNull(Me!SFirst) And Not IsNull(Me!SLast) Then
strSQL = ""
strSQL = strSQL & "SELECT * FROM [tblemp] "
strSQL = strSQL & "WHERE trim([FirstName]) Like """ &
Trim(Me!SFirst) & "*"""
strSQL = strSQL & " AND trim([LastName]) Like """ & Trim(Me!SLast) &
"*"""
strSQL = strSQL & " ORDER BY [LastName] ASC;"
Me.RecordSource = strSQL
'If First Name (SFirst) name has data then restrict by First Name.
ElseIf Not IsNull(Me!SFirst) Then
strSQL = ""
strSQL = strSQL & "SELECT * FROM [tblemp] "
strSQL = strSQL & "WHERE trim(FirstName) Like """ & Trim(Me!SFirst)
& "*"""
strSQL = strSQL & " ORDER BY [LastName] ASC;"
Me.RecordSource = strSQL
'If Last (SLast) name has data then restrict by Last Name.
ElseIf Not IsNull(Me!SLast) Then
strSQL = ""
strSQL = strSQL & "SELECT * FROM [tblemp] "
strSQL = strSQL & "WHERE trim([LastName]) Like """ & Trim(Me!SLast)
& "*"""
strSQL = strSQL & "ORDER BY [LastName] ASC;"
Me.RecordSource = strSQL
'If Phone (SPhone) number has data then restrict by the phone number.
ElseIf Not IsNull(Me!SPhone) Then
strSQL = ""
strSQL = strSQL & "SELECT * FROM [tblemp] "
strSQL = strSQL & "WHERE [workphone] Like '*" & Trim(Me!SPhone) & "*'"
strSQL = strSQL & "ORDER BY [LastName] ASC;"
Me.RecordSource = strSQL
'If EmpID (SEmpID) has data then restrict by the Employee ID.
ElseIf Not IsNull(Me!SEmpID) Then
strSQL = ""
strSQL = strSQL & "SELECT * FROM [tblemp] "
strSQL = strSQL & "WHERE [EmployeeID] = " & Trim(Me!SEmpID) & " "
strSQL = strSQL & "ORDER BY [LastName] ASC;"
Me.RecordSource = strSQL
End If
' Empty out the search criteria
Me!SFirst = Null
Me!SLast = Null
Me!SPhone = Null
Me!SEmpID = Null

End Sub


******** Code End **************************



Zenon said:
I am having trouble with a simple filter, hope someone can help. I
have a form based on a query. I am trying to filter the form's output
by the text of a textbox. The event is a command button. Here is my
attempts at the code, none of which worked

Private Sub btn_Filter_Description_Click()

Me.Description.SetFocus
If (IsNull(Me.Description) Or Me.Description.Text = "") Then
MsgBox ("Cannot filter by blank value")
Else
Dim stmt As String
Dim arg As String
Me.Description.SetFocus
'stmt = Me.Description.Text
arg = Forms![form_Incoming].Description.Text

stmt = BuildCriteria("Description", dbText, arg)
Me.Filter = stmt
'Me.Description.SetFocus
'Me.Form.Filter = "Description.Text = " & stmt
'Me.Form.FilterOn = True

End If

End Sub




thanks,

Zenon
Click to expand...
 
Zenon,

     I build a "filter" in a similar manor but I just use the recordset.

I have text boxes on my from called SLast, SFirst, SPhone, and SEmpID

Then two CMD buttons, one to search (cmdSearch_Click) and one to reset to
the original state.

In my code if both first and last are filled in it will use both of them.

********  Code Start  ************************

Private Sub cmdSearch_Click()
  Dim strSQL As String
'If both First (SFirst) and Last (SLast) name have data entered then
restrict using both.
    If Not IsNull(Me!SFirst) And Not IsNull(Me!SLast) Then
        strSQL = ""
        strSQL = strSQL & "SELECT * FROM [tblemp] "
        strSQL = strSQL & "WHERE trim([FirstName]) Like """ &
Trim(Me!SFirst) & "*"""
        strSQL = strSQL & " AND trim([LastName]) Like """ & Trim(Me!SLast) &
"*"""
        strSQL = strSQL & " ORDER BY [LastName] ASC;"
        Me.RecordSource = strSQL
'If First Name (SFirst) name has data then restrict by First Name.
    ElseIf Not IsNull(Me!SFirst) Then
        strSQL = ""
        strSQL = strSQL & "SELECT * FROM [tblemp] "
        strSQL = strSQL & "WHERE trim(FirstName) Like """ & Trim(Me!SFirst)
& "*"""
        strSQL = strSQL & " ORDER BY [LastName] ASC;"
        Me.RecordSource = strSQL
'If Last (SLast) name has data then restrict by Last Name.
    ElseIf Not IsNull(Me!SLast) Then
        strSQL = ""
        strSQL = strSQL & "SELECT * FROM [tblemp] "
        strSQL = strSQL & "WHERE trim([LastName]) Like """ & Trim(Me!SLast)
& "*"""
        strSQL = strSQL & "ORDER BY [LastName] ASC;"
        Me.RecordSource = strSQL
'If Phone (SPhone) number has data then restrict by the phone number.
    ElseIf Not IsNull(Me!SPhone) Then
        strSQL = ""
        strSQL = strSQL & "SELECT * FROM [tblemp] "
        strSQL = strSQL & "WHERE [workphone] Like '*" & Trim(Me!SPhone) & "*'"
        strSQL = strSQL & "ORDER BY [LastName] ASC;"
        Me.RecordSource = strSQL
'If EmpID (SEmpID) has data then restrict by the Employee ID.
    ElseIf Not IsNull(Me!SEmpID) Then
        strSQL = ""
        strSQL = strSQL & "SELECT * FROM [tblemp] "
        strSQL = strSQL & "WHERE [EmployeeID] = " & Trim(Me!SEmpID) & " "
        strSQL = strSQL & "ORDER BY [LastName] ASC;"
        Me.RecordSource = strSQL
    End If
' Empty out the search criteria
        Me!SFirst = Null
        Me!SLast = Null
        Me!SPhone = Null
        Me!SEmpID = Null

End Sub

********  Code End  **************************



Zenon said:
I am having trouble with a simple filter, hope someone can help.  I
have a form based on a query.  I am trying to filter the form's output
by the text of a textbox.  The event is a command button.  Here is my
attempts at the code, none of which worked
Click to expand...
Private Sub btn_Filter_Description_Click()
Click to expand...
Me.Description.SetFocus
    If (IsNull(Me.Description) Or Me.Description.Text = "") Then
        MsgBox ("Cannot filter by blank value")
Else
Dim stmt As String
Dim arg As String
Me.Description.SetFocus
'stmt = Me.Description.Text
arg = Forms![form_Incoming].Description.Text
Click to expand...
    stmt = BuildCriteria("Description", dbText, arg)
    Me.Filter = stmt
    'Me.Description.SetFocus
    'Me.Form.Filter = "Description.Text = " & stmt
    'Me.Form.FilterOn = True
Click to expand...
End If
Click to expand...
End Sub

Zenon- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Beautiful! thank you very much Ivan. I didn't know that I could
limit the recordset so easily. Worked like a charm. Thanks again.

Zenon
 
Zenon,
Click to expand...
     I build a "filter" in a similar manor but I just use the record set.
Click to expand...
I have text boxes on my from called SLast, SFirst, SPhone, and SEmpID
Click to expand...
Then two CMD buttons, one to search (cmdSearch_Click) and one to reset to
the original state.
Click to expand...
In my code if both first and last are filled in it will use both of them..
Click to expand...
********  Code Start  ************************
Click to expand...
Private Sub cmdSearch_Click()
  Dim strSQL As String
'If both First (SFirst) and Last (SLast) name have data entered then
restrict using both.
    If Not IsNull(Me!SFirst) And Not IsNull(Me!SLast) Then
        strSQL = ""
        strSQL = strSQL & "SELECT * FROM [tblemp] "
        strSQL = strSQL & "WHERE trim([FirstName]) Like """ &
Trim(Me!SFirst) & "*"""
        strSQL = strSQL & " AND trim([LastName]) Like """ & Trim(Me!SLast) &
"*"""
        strSQL = strSQL & " ORDER BY [LastName] ASC;"
        Me.RecordSource = strSQL
'If First Name (SFirst) name has data then restrict by First Name.
    ElseIf Not IsNull(Me!SFirst) Then
        strSQL = ""
        strSQL = strSQL & "SELECT * FROM [tblemp] "
        strSQL = strSQL & "WHERE trim(FirstName) Like """ & Trim(Me!SFirst)
& "*"""
        strSQL = strSQL & " ORDER BY [LastName] ASC;"
        Me.RecordSource = strSQL
'If Last (SLast) name has data then restrict by Last Name.
    ElseIf Not IsNull(Me!SLast) Then
        strSQL = ""
        strSQL = strSQL & "SELECT * FROM [tblemp] "
        strSQL = strSQL & "WHERE trim([LastName]) Like """ & Trim(Me!SLast)
& "*"""
        strSQL = strSQL & "ORDER BY [LastName] ASC;"
        Me.RecordSource = strSQL
'If Phone (SPhone) number has data then restrict by the phone number.
    ElseIf Not IsNull(Me!SPhone) Then
        strSQL = ""
        strSQL = strSQL & "SELECT * FROM [tblemp] "
        strSQL = strSQL & "WHERE [workphone] Like '*" & Trim(Me!SPhone) & "*'"
        strSQL = strSQL & "ORDER BY [LastName] ASC;"
        Me.RecordSource = strSQL
'If EmpID (SEmpID) has data then restrict by the Employee ID.
    ElseIf Not IsNull(Me!SEmpID) Then
        strSQL = ""
        strSQL = strSQL & "SELECT * FROM [tblemp] "
        strSQL = strSQL & "WHERE [EmployeeID] = " & Trim(Me!SEmpID) & " "
        strSQL = strSQL & "ORDER BY [LastName] ASC;"
        Me.RecordSource = strSQL
    End If
' Empty out the search criteria
        Me!SFirst = Null
        Me!SLast = Null
        Me!SPhone = Null
        Me!SEmpID = Null
Click to expand...
End Sub
Click to expand...
********  Code End  **************************
Click to expand...
Zenon said:
I am having trouble with a simple filter, hope someone can help.  I
have a form based on a query.  I am trying to filter the form's output
by the text of a textbox.  The event is a command button.  Here ismy
attempts at the code, none of which worked
Private Sub btn_Filter_Description_Click()
Me.Description.SetFocus
    If (IsNull(Me.Description) Or Me.Description.Text = "") Then
        MsgBox ("Cannot filter by blank value")
Else
Dim stmt As String
Dim arg As String
Me.Description.SetFocus
'stmt = Me.Description.Text
arg = Forms![form_Incoming].Description.Text
    stmt = BuildCriteria("Description", dbText, arg)
    Me.Filter = stmt
    'Me.Description.SetFocus
    'Me.Form.Filter = "Description.Text = " & stmt
    'Me.Form.FilterOn = True
End If
End Sub
thanks,
Zenon- Hide quoted text -
Click to expand...
Click to expand...
- Show quoted text -
Click to expand...

Beautiful!  thank you very much Ivan.  I didn't know that I could
limit the recordset so easily.  Worked like a charm.  Thanks again.

Zenon- Hide quoted text -

- Show quoted text I
Click to expand...




I have one final question on this topic. I need to add the ability to
filter the date within a range. My code doesn't seem to be working.
Any comments or suggestions are greatly appreciated

Private Sub btn_Filter_Date_Click()

Me.Date__Recd.SetFocus
If (IsNull(Me.Date__Recd) Or Me.Date__Recd.Text = "") Then
MsgBox ("Cannot filter by blank value")
Else
Dim arg As String
Dim arg2 As String
Me.tb_Date_From.SetFocus
arg = Me.tb_Date_From.Text
Me.tb_Date_To.SetFocus
arg2 = Me.tb_Date_To.Text
Dim SQLString As String
SQLString = ""

SQLString = SQLString & "SELECT * FROM [Query_Incoming]"
SQLString = SQLString & "WHERE [Table_PPE_Incoming!Date_Recd] >="
& arg
SQLString = SQLString & " AND [Table_PPE_Incoming!Date_Recd] <=" &
arg2 & ""
Me.RecordSource = SQLString

End If

End Sub
 
Back
Top