Filter on form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a main form based on a table. I would like to give the users the
option to show only records with a certain field entry, records without this
certain entry, and all records by choosing the appropriate button. Any ideas?
 
Place some unbound controls at the top of the form (Form Header section?)
where the user can enter the filtering values. Suggested:
- a combo containing a Value List such as 1;"Is";2;"Is Not";
- a text box where the user enters the value to match;
- a command button for Apply Filter;
- a command button for Remove Filter.

In the click event procedure of cmdApplyFilter, build the filter string.
This example shows how to apply it to a Text field.
For a Number field, remove the line:
strDelim = """"
For a Date/Time field, use:
strDelim = "#"

-----------------------code starts-------------------
Dim strField As String 'Name of field to filter on.
Dim strDelim As String 'Delimiter for type of field.
Dim strFilter As String

strField = "[Surname]"
strDelim = """"
If IsNull(Me.cboOp) Or IsNull(Me.txtFind) Then
MsgBox "Must enter both combo and text box values."
Else
If Me.Dirty Then 'Save first.
Me.Dirty = False
End If

'Create the filter string
Select Case Me.cboOp
Case 1 'Is
strFilter = strField & " = " & strDelim & Me.txtFind & strDelim
Case 2 'Is Not
strFilter = strField & " <> " & strDelim & Me.txtFind & strDelim
Case Else
Debug.Print "Combo value not handled."
End Select

'Apply the filter
If Len(strFilter) > 0 Then
Me.Filter = strFilter
Me.FilterOn = True
End If
End If
-----------------------code ends-------------------

The code is written so it is very easy to extend. For example you could
provide an option in the combo for "Begins with", and in that case:
strFilter = strField & " Like " & strDelim & Me.txtFind & "*" & strDelim

Or an option for "Blank", and handle the case as:
strFilter = strField & " Is Null"
 
How would you apply the code below to a yes/no field? And does this code do
both the apply and remove filter. Should I use a toggle button instead of a
cmd button?

Allen Browne said:
Place some unbound controls at the top of the form (Form Header section?)
where the user can enter the filtering values. Suggested:
- a combo containing a Value List such as 1;"Is";2;"Is Not";
- a text box where the user enters the value to match;
- a command button for Apply Filter;
- a command button for Remove Filter.

In the click event procedure of cmdApplyFilter, build the filter string.
This example shows how to apply it to a Text field.
For a Number field, remove the line:
strDelim = """"
For a Date/Time field, use:
strDelim = "#"

-----------------------code starts-------------------
Dim strField As String 'Name of field to filter on.
Dim strDelim As String 'Delimiter for type of field.
Dim strFilter As String

strField = "[Surname]"
strDelim = """"
If IsNull(Me.cboOp) Or IsNull(Me.txtFind) Then
MsgBox "Must enter both combo and text box values."
Else
If Me.Dirty Then 'Save first.
Me.Dirty = False
End If

'Create the filter string
Select Case Me.cboOp
Case 1 'Is
strFilter = strField & " = " & strDelim & Me.txtFind & strDelim
Case 2 'Is Not
strFilter = strField & " <> " & strDelim & Me.txtFind & strDelim
Case Else
Debug.Print "Combo value not handled."
End Select

'Apply the filter
If Len(strFilter) > 0 Then
Me.Filter = strFilter
Me.FilterOn = True
End If
End If
-----------------------code ends-------------------

The code is written so it is very easy to extend. For example you could
provide an option in the combo for "Begins with", and in that case:
strFilter = strField & " Like " & strDelim & Me.txtFind & "*" & strDelim

Or an option for "Blank", and handle the case as:
strFilter = strField & " Is Null"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

debraj007 said:
I have a main form based on a table. I would like to give the users the
option to show only records with a certain field entry, records without
this
certain entry, and all records by choosing the appropriate button. Any
ideas?
Click to expand...
Click to expand...
 
For a Yes/No field, you want to give the user 3 options:
- value is Yes (true, the value -1),
- value is No (false, the value 0),
- don't filter on this field (null).
Although it is possible to use a triple state toggle button or check box,
the 3 states are not very obvious to the user, and under Windows XP the 3rd
state is not visually distinguished at all.

My personal preference is therefore to provide a combo as the interface. For
a yes/no field named "Inactive", I would use a combo with these properties:
Name cboFilterInactive
RowSourceType Value List
RowSource -1;"Inactive";0;"Active"
ColumnCount 2
ColumnWidths 0

Then the code would contain:
If Not IsNull(Me.cboFilterInactive) Then
strFilter = "[Inactive] = " & Me.cboFilterInactive
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Franca said:
How would you apply the code below to a yes/no field? And does this code
do
both the apply and remove filter. Should I use a toggle button instead of
a
cmd button?

Allen Browne said:
Place some unbound controls at the top of the form (Form Header section?)
where the user can enter the filtering values. Suggested:
- a combo containing a Value List such as 1;"Is";2;"Is Not";
- a text box where the user enters the value to match;
- a command button for Apply Filter;
- a command button for Remove Filter.

In the click event procedure of cmdApplyFilter, build the filter string.
This example shows how to apply it to a Text field.
For a Number field, remove the line:
strDelim = """"
For a Date/Time field, use:
strDelim = "#"

-----------------------code starts-------------------
Dim strField As String 'Name of field to filter on.
Dim strDelim As String 'Delimiter for type of field.
Dim strFilter As String

strField = "[Surname]"
strDelim = """"
If IsNull(Me.cboOp) Or IsNull(Me.txtFind) Then
MsgBox "Must enter both combo and text box values."
Else
If Me.Dirty Then 'Save first.
Me.Dirty = False
End If

'Create the filter string
Select Case Me.cboOp
Case 1 'Is
strFilter = strField & " = " & strDelim & Me.txtFind &
strDelim
Case 2 'Is Not
strFilter = strField & " <> " & strDelim & Me.txtFind &
strDelim
Case Else
Debug.Print "Combo value not handled."
End Select

'Apply the filter
If Len(strFilter) > 0 Then
Me.Filter = strFilter
Me.FilterOn = True
End If
End If
-----------------------code ends-------------------

The code is written so it is very easy to extend. For example you could
provide an option in the combo for "Begins with", and in that case:
strFilter = strField & " Like " & strDelim & Me.txtFind & "*" &
strDelim

Or an option for "Blank", and handle the case as:
strFilter = strField & " Is Null"


debraj007 said:
I have a main form based on a table. I would like to give the users the
option to show only records with a certain field entry, records without
this
certain entry, and all records by choosing the appropriate button. Any
ideas?
Click to expand...
Click to expand...
Click to expand...
 
I created the combo but not sure how to incorporate this into my yes/no check
box.

Would I need to add this code:

If Not IsNull(Me.cboFilterInactive) Then
strFilter = "[Inactive] = " & Me.cboFilterInactive
Click to expand...
End If

to the code that you already have here? Or does it replace something. Also
do I need the following in the code:

strField = "[Surname]" - this would be "[name of yes/no field]" ?
strDelim = """" - do I need to have the delimiter?

Thanks for your help.

Allen Browne said:
For a Yes/No field, you want to give the user 3 options:
- value is Yes (true, the value -1),
- value is No (false, the value 0),
- don't filter on this field (null).
Although it is possible to use a triple state toggle button or check box,
the 3 states are not very obvious to the user, and under Windows XP the 3rd
state is not visually distinguished at all.

My personal preference is therefore to provide a combo as the interface. For
a yes/no field named "Inactive", I would use a combo with these properties:
Name cboFilterInactive
RowSourceType Value List
RowSource -1;"Inactive";0;"Active"
ColumnCount 2
ColumnWidths 0

Then the code would contain:
If Not IsNull(Me.cboFilterInactive) Then
strFilter = "[Inactive] = " & Me.cboFilterInactive
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Franca said:
How would you apply the code below to a yes/no field? And does this code
do
both the apply and remove filter. Should I use a toggle button instead of
a
cmd button?

Allen Browne said:
Place some unbound controls at the top of the form (Form Header section?)
where the user can enter the filtering values. Suggested:
- a combo containing a Value List such as 1;"Is";2;"Is Not";
- a text box where the user enters the value to match;
- a command button for Apply Filter;
- a command button for Remove Filter.

In the click event procedure of cmdApplyFilter, build the filter string.
This example shows how to apply it to a Text field.
For a Number field, remove the line:
strDelim = """"
For a Date/Time field, use:
strDelim = "#"

-----------------------code starts-------------------
Dim strField As String 'Name of field to filter on.
Dim strDelim As String 'Delimiter for type of field.
Dim strFilter As String

strField = "[Surname]"
strDelim = """"
If IsNull(Me.cboOp) Or IsNull(Me.txtFind) Then
MsgBox "Must enter both combo and text box values."
Else
If Me.Dirty Then 'Save first.
Me.Dirty = False
End If

'Create the filter string
Select Case Me.cboOp
Case 1 'Is
strFilter = strField & " = " & strDelim & Me.txtFind &
strDelim
Case 2 'Is Not
strFilter = strField & " <> " & strDelim & Me.txtFind &
strDelim
Case Else
Debug.Print "Combo value not handled."
End Select

'Apply the filter
If Len(strFilter) > 0 Then
Me.Filter = strFilter
Me.FilterOn = True
End If
End If
-----------------------code ends-------------------

The code is written so it is very easy to extend. For example you could
provide an option in the combo for "Begins with", and in that case:
strFilter = strField & " Like " & strDelim & Me.txtFind & "*" &
strDelim

Or an option for "Blank", and handle the case as:
strFilter = strField & " Is Null"


I have a main form based on a table. I would like to give the users the
option to show only records with a certain field entry, records without
this
certain entry, and all records by choosing the appropriate button. Any
ideas?
Click to expand...
Click to expand...
Click to expand...
 
Yes, that's the idea.

If you Debug.Printer strFilter, you should see:
[Inactive] = -1
or for true
[Inactive] = 0
for false.

No delimiter.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Franca said:
I created the combo but not sure how to incorporate this into my yes/no
check
box.

Would I need to add this code:

If Not IsNull(Me.cboFilterInactive) Then
strFilter = "[Inactive] = " & Me.cboFilterInactive
Click to expand...
End If

to the code that you already have here? Or does it replace something.
Also
do I need the following in the code:

strField = "[Surname]" - this would be "[name of yes/no field]" ?
strDelim = """" - do I need to have the delimiter?

Thanks for your help.

Allen Browne said:
For a Yes/No field, you want to give the user 3 options:
- value is Yes (true, the value -1),
- value is No (false, the value 0),
- don't filter on this field (null).
Although it is possible to use a triple state toggle button or check box,
the 3 states are not very obvious to the user, and under Windows XP the
3rd
state is not visually distinguished at all.

My personal preference is therefore to provide a combo as the interface.
For
a yes/no field named "Inactive", I would use a combo with these
properties:
Name cboFilterInactive
RowSourceType Value List
RowSource -1;"Inactive";0;"Active"
ColumnCount 2
ColumnWidths 0

Then the code would contain:
If Not IsNull(Me.cboFilterInactive) Then
strFilter = "[Inactive] = " & Me.cboFilterInactive
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Franca said:
How would you apply the code below to a yes/no field? And does this
code
do
both the apply and remove filter. Should I use a toggle button instead
of
a
cmd button?

:

Place some unbound controls at the top of the form (Form Header
section?)
where the user can enter the filtering values. Suggested:
- a combo containing a Value List such as 1;"Is";2;"Is Not";
- a text box where the user enters the value to match;
- a command button for Apply Filter;
- a command button for Remove Filter.

In the click event procedure of cmdApplyFilter, build the filter
string.
This example shows how to apply it to a Text field.
For a Number field, remove the line:
strDelim = """"
For a Date/Time field, use:
strDelim = "#"

-----------------------code starts-------------------
Dim strField As String 'Name of field to filter on.
Dim strDelim As String 'Delimiter for type of field.
Dim strFilter As String

strField = "[Surname]"
strDelim = """"
If IsNull(Me.cboOp) Or IsNull(Me.txtFind) Then
MsgBox "Must enter both combo and text box values."
Else
If Me.Dirty Then 'Save first.
Me.Dirty = False
End If

'Create the filter string
Select Case Me.cboOp
Case 1 'Is
strFilter = strField & " = " & strDelim & Me.txtFind &
strDelim
Case 2 'Is Not
strFilter = strField & " <> " & strDelim & Me.txtFind &
strDelim
Case Else
Debug.Print "Combo value not handled."
End Select

'Apply the filter
If Len(strFilter) > 0 Then
Me.Filter = strFilter
Me.FilterOn = True
End If
End If
-----------------------code ends-------------------

The code is written so it is very easy to extend. For example you
could
provide an option in the combo for "Begins with", and in that case:
strFilter = strField & " Like " & strDelim & Me.txtFind & "*" &
strDelim

Or an option for "Blank", and handle the case as:
strFilter = strField & " Is Null"


I have a main form based on a table. I would like to give the users
the
option to show only records with a certain field entry, records
without
this
certain entry, and all records by choosing the appropriate button.
Any
ideas?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top