Re: Multiple Filters in a Form

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

"Wolfgang Kais" wrote:

Hello Rob.

"RobDavo" wrote:
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.

--
Regards,
Wolfgang



.

Relevant Pages

  • Re: How to print all records in the active form?
    ... Loop through the RecordsetClone of the form, concatenating the primary key value into a string. ... I cant see what to filter on that could make this work. ... If this cant help i ... Dim strWhere As String ...
    (microsoft.public.access.reports)
  • Re: Searching via combo box
    ... strWhere = Left$ ... filter and that works just fine. ... When you're building a SQL string like this, ... note the single quotes around the company name. ...
    (microsoft.public.access.formscoding)
  • RE: Check Box as filter
    ... filter displays double of records. ... check box control theres no records in controls of detail section. ... Dim strWhere As String ...
    (microsoft.public.access.formscoding)
  • RE: Multiple comboboxes filtering each other
    ... If lngLen <= 0 Then 'Nah: there was nothing in the string. ... DoCmd.Close acForm, "editPatientInfo" ... 'Debug.Print strWhere ... apply the string as the form's Filter. ...
    (microsoft.public.access.forms)
  • Re: Allen Brownes Search2000
    ... strWhere = "NOT EXISTS (SELECT tbl_trans_buyer_seller.RecdID FROM ... I read your article on subquery basics and am still ... 'Chop off the trailing " AND ", and use the string as the form's Filter. ...
    (microsoft.public.access.formscoding)