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
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 Sub
Zenon- Hide quoted text -
- Show quoted text -