Multiple Filters in a Form

[RobDavo]

Hi,
I need some help. I have a form based on a query which lists wine in a
cellar. I want to allow the users to filter the content on the form by using
multiple filters. The following code works well.

Private Sub CmdApplyFilter_Click()
Dim strWhere As String
strWhere = ""

Dim strNonZero As String
Dim strType As String
Dim strYear As String

strNonZero = ""
strType = ""
strYear = ""

' from a check box to show either only current wine in cellar or all wine
included that which has been drunk - no bottles in cellar
If CheckNonZero = True Then
strNonZero = "Bottle_remaining > 0"
Else
strNonZero = "Bottle_remaining >= 0"
End If
' select "red", "white", etc
If Not IsNull([ComboSelectType]) Then
strType = " And Type= '" & ComboSelectType & "'"
End If
' select year of the wine
If Not IsNull([ComboYear]) Then
strYear = " And Year= '" & ComboYear & "'"
End If

strWhere = strNonZero & strType & strYear
DoCmd.ApplyFilter , strWhere
FilterOn = True

End Sub

The problem occurs when the user clears the filter, re-selects parameters
and re-filters the form.
The form's property "Filter" retains values not selected, eg when the user
selects all 3 values and then re-filter only 2 of these and the filter
property keeps all 3 eg "Bottle_remaining > 0 And Type= 'Red' And Year= '' "
which is then not a valid Where statement and no records are shown.
This problem is cleared by closing and re-opening the form.

Any suggestions would be appreciated.
Thanks
Rob

 

[Wolfgang Kais]

Hello Rob.

Hi,
I need some help. I have a form based on a query which lists wine
in a cellar. I want to allow the users to filter the content on the
form by using multiple filters. The following code works well.

Private Sub CmdApplyFilter_Click()
Dim strWhere As String
strWhere = ""

Dim strNonZero As String
Dim strType As String
Dim strYear As String

strNonZero = ""
strType = ""
strYear = ""

' from a check box to show either only current wine in cellar or all wine
' included that which has been drunk - no bottles in cellar
If CheckNonZero = True Then
strNonZero = "Bottle_remaining > 0"
Else
strNonZero = "Bottle_remaining >= 0"
End If
' select "red", "white", etc
If Not IsNull([ComboSelectType]) Then
strType = " And Type= '" & ComboSelectType & "'"
End If
' select year of the wine
If Not IsNull([ComboYear]) Then
strYear = " And Year= '" & ComboYear & "'"
End If

strWhere = strNonZero & strType & strYear
DoCmd.ApplyFilter , strWhere
FilterOn = True

End Sub

The problem occurs when the user clears the filter, re-selects
parameters and re-filters the form.
The form's property "Filter" retains values not selected, eg when
the user selects all 3 values and then re-filter only 2 of these
and the filter property keeps all 3 eg "Bottle_remaining > 0 And
Type= 'Red' And Year= '' " which is then not a valid Where
statement and no records are shown. This problem is cleared by
closing and re-opening the form.

First: When using DoCmd.ApplyFilter, you can delete the next
statement (FilterOn = True), but that's not the problem.
How do you "Clear" the filter? I suppose by using by setting the
values of some controls to an empty string ("").
I suggest setting them to Null instead.

 

[RobDavo]

Wolfgang

Thanks for the response. My understanding that "" is the same as Null for a
string, is this correct? I am not sure how to set a string to null other than
via "".

On viewing the filter grid / SQL via Records, Filters, Advanced
Filters/Sort, the fields from the prefious filterr are not cleared. Manually
clearing the fields from the grid does not work either.

Any further thoughts would be great

Thanks
Rob

Hello Rob.

Hi,
I need some help. I have a form based on a query which lists wine
in a cellar. I want to allow the users to filter the content on the
form by using multiple filters. The following code works well.

Private Sub CmdApplyFilter_Click()
Dim strWhere As String
strWhere = ""

Dim strNonZero As String
Dim strType As String
Dim strYear As String

strNonZero = ""
strType = ""
strYear = ""

' from a check box to show either only current wine in cellar or all wine
' included that which has been drunk - no bottles in cellar
If CheckNonZero = True Then
strNonZero = "Bottle_remaining > 0"
Else
strNonZero = "Bottle_remaining >= 0"
End If
' select "red", "white", etc
If Not IsNull([ComboSelectType]) Then
strType = " And Type= '" & ComboSelectType & "'"
End If
' select year of the wine
If Not IsNull([ComboYear]) Then
strYear = " And Year= '" & ComboYear & "'"
End If

strWhere = strNonZero & strType & strYear
DoCmd.ApplyFilter , strWhere
FilterOn = True

End Sub

The problem occurs when the user clears the filter, re-selects
parameters and re-filters the form.
The form's property "Filter" retains values not selected, eg when
the user selects all 3 values and then re-filter only 2 of these
and the filter property keeps all 3 eg "Bottle_remaining > 0 And
Type= 'Red' And Year= '' " which is then not a valid Where
statement and no records are shown. This problem is cleared by
closing and re-opening the form.

First: When using DoCmd.ApplyFilter, you can delete the next
statement (FilterOn = True), but that's not the problem.
How do you "Clear" the filter? I suppose by using by setting the
values of some controls to an empty string ("").
I suggest setting them to Null instead.

 

[Douglas J. Steele]

Your understanding is incorrect. "" and Null are definitely not the same
thing. Strings cannot hold Null values, though: the only variable type that
can hold Nulls values is the Variant.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Wolfgang

Thanks for the response. My understanding that "" is the same as Null for
a
string, is this correct? I am not sure how to set a string to null other
than
via "".

On viewing the filter grid / SQL via Records, Filters, Advanced
Filters/Sort, the fields from the prefious filterr are not cleared.
Manually
clearing the fields from the grid does not work either.

Any further thoughts would be great

Thanks
Rob

Hello Rob.

Hi,
I need some help. I have a form based on a query which lists wine
in a cellar. I want to allow the users to filter the content on the
form by using multiple filters. The following code works well.

Private Sub CmdApplyFilter_Click()
Dim strWhere As String
strWhere = ""

Dim strNonZero As String
Dim strType As String
Dim strYear As String

strNonZero = ""
strType = ""
strYear = ""

' from a check box to show either only current wine in cellar or all
wine
' included that which has been drunk - no bottles in cellar
If CheckNonZero = True Then
strNonZero = "Bottle_remaining > 0"
Else
strNonZero = "Bottle_remaining >= 0"
End If
' select "red", "white", etc
If Not IsNull([ComboSelectType]) Then
strType = " And Type= '" & ComboSelectType & "'"
End If
' select year of the wine
If Not IsNull([ComboYear]) Then
strYear = " And Year= '" & ComboYear & "'"
End If

strWhere = strNonZero & strType & strYear
DoCmd.ApplyFilter , strWhere
FilterOn = True

End Sub

The problem occurs when the user clears the filter, re-selects
parameters and re-filters the form.
The form's property "Filter" retains values not selected, eg when
the user selects all 3 values and then re-filter only 2 of these
and the filter property keeps all 3 eg "Bottle_remaining > 0 And
Type= 'Red' And Year= '' " which is then not a valid Where
statement and no records are shown. This problem is cleared by
closing and re-opening the form.

First: When using DoCmd.ApplyFilter, you can delete the next
statement (FilterOn = True), but that's not the problem.
How do you "Clear" the filter? I suppose by using by setting the
values of some controls to an empty string ("").
I suggest setting them to Null instead.

 

[Wolfgang Kais]

Hello Rob.

:

Hi,
I need some help. I have a form based on a query which lists wine
in a cellar. I want to allow the users to filter the content on
the form by using multiple filters. The following code works well.

Private Sub CmdApplyFilter_Click()
Dim strWhere As String
strWhere = ""

Dim strNonZero As String
Dim strType As String
Dim strYear As String

strNonZero = ""
strType = ""
strYear = ""

' from a check box to show either only current wine in cellar or all
wine
' included that which has been drunk - no bottles in cellar
If CheckNonZero = True Then
strNonZero = "Bottle_remaining > 0"
Else
strNonZero = "Bottle_remaining >= 0"
End If
' select "red", "white", etc
If Not IsNull([ComboSelectType]) Then
strType = " And Type= '" & ComboSelectType & "'"
End If
' select year of the wine
If Not IsNull([ComboYear]) Then
strYear = " And Year= '" & ComboYear & "'"
End If

strWhere = strNonZero & strType & strYear
DoCmd.ApplyFilter , strWhere
FilterOn = True

End Sub

The problem occurs when the user clears the filter, re-selects
parameters and re-filters the form.
The form's property "Filter" retains values not selected, eg when
the user selects all 3 values and then re-filter only 2 of these
and the filter property keeps all 3 eg "Bottle_remaining > 0 And
Type= 'Red' And Year= '' " which is then not a valid Where
statement and no records are shown. This problem is cleared by
closing and re-opening the form.

First: When using DoCmd.ApplyFilter, you can delete the next
statement (FilterOn = True), but that's not the problem.
How do you "Clear" the filter? I suppose by using by setting the
values of some controls to an empty string ("").
I suggest setting them to Null instead.

Thanks for the response. My understanding that "" is the same as
Null for a string, is this correct? I am not sure how to set a
string to null other than via "".

On viewing the filter grid / SQL via Records, Filters, Advanced
Filters/Sort, the fields from the prefious filterr are not
cleared. Manually clearing the fields from the grid does not work
either.

Any further thoughts would be great

My test showed that ApplyFilter clears the previous filter and sets
a new filter for the form, so this is not the problem. The problem
is that your code checks for NULL in ComboSelectType and ComboYear
but they do not contain Null id the users cleard the filter.
A string can never be null.
Null and "" is not the same.
Null is the value that is stored in a database field (or a control)
when the field/control has no value ("" IS a string value).
You must not try to assign Null to a string variable but assign Null
to ComboSelectType and ComboYear, for example:
CheckNonZero = False
ComboSelectType = Null
ComboYear = Null
If you don't want to use the Null value, you will have to change your
code (this will also work when using Null to clear the filter):
[...]
If Nz([ComboSelectType], "") <> "" Then
strType = " And Type= '" & ComboSelectType & "'"
End If
' select year of the wine
If Nz([ComboYear], "") <> "" Then
strYear = " And Year= '" & ComboYear & "'"
End If

 

[RobDavo]

Wolfgang & Doug

Thanks for the clarification on this.

Wolfgang, thanks for the suggestion, it has worked like treat and is very
appreciated

Regards
Rob

Hello Rob.

:

Hi,
I need some help. I have a form based on a query which lists wine
in a cellar. I want to allow the users to filter the content on
the form by using multiple filters. The following code works well.

Private Sub CmdApplyFilter_Click()
Dim strWhere As String
strWhere = ""

Dim strNonZero As String
Dim strType As String
Dim strYear As String

strNonZero = ""
strType = ""
strYear = ""

' from a check box to show either only current wine in cellar or all
wine
' included that which has been drunk - no bottles in cellar
If CheckNonZero = True Then
strNonZero = "Bottle_remaining > 0"
Else
strNonZero = "Bottle_remaining >= 0"
End If
' select "red", "white", etc
If Not IsNull([ComboSelectType]) Then
strType = " And Type= '" & ComboSelectType & "'"
End If
' select year of the wine
If Not IsNull([ComboYear]) Then
strYear = " And Year= '" & ComboYear & "'"
End If

strWhere = strNonZero & strType & strYear
DoCmd.ApplyFilter , strWhere
FilterOn = True

End Sub

The problem occurs when the user clears the filter, re-selects
parameters and re-filters the form.
The form's property "Filter" retains values not selected, eg when
the user selects all 3 values and then re-filter only 2 of these
and the filter property keeps all 3 eg "Bottle_remaining > 0 And
Type= 'Red' And Year= '' " which is then not a valid Where
statement and no records are shown. This problem is cleared by
closing and re-opening the form.
First: When using DoCmd.ApplyFilter, you can delete the next
statement (FilterOn = True), but that's not the problem.
How do you "Clear" the filter? I suppose by using by setting the
values of some controls to an empty string ("").
I suggest setting them to Null instead.

Thanks for the response. My understanding that "" is the same as
Null for a string, is this correct? I am not sure how to set a
string to null other than via "".

On viewing the filter grid / SQL via Records, Filters, Advanced
Filters/Sort, the fields from the prefious filterr are not
cleared. Manually clearing the fields from the grid does not work
either.

Any further thoughts would be great

My test showed that ApplyFilter clears the previous filter and sets
a new filter for the form, so this is not the problem. The problem
is that your code checks for NULL in ComboSelectType and ComboYear
but they do not contain Null id the users cleard the filter.
A string can never be null.
Null and "" is not the same.
Null is the value that is stored in a database field (or a control)
when the field/control has no value ("" IS a string value).
You must not try to assign Null to a string variable but assign Null
to ComboSelectType and ComboYear, for example:
CheckNonZero = False
ComboSelectType = Null
ComboYear = Null
If you don't want to use the Null value, you will have to change your
code (this will also work when using Null to clear the filter):
[...]
If Nz([ComboSelectType], "") <> "" Then
strType = " And Type= '" & ComboSelectType & "'"
End If
' select year of the wine
If Nz([ComboYear], "") <> "" Then
strYear = " And Year= '" & ComboYear & "'"
End If