Complex multiple filters

[ali32j]

Hi All

I have a form which i am filtering to multiple combo box selections using the below code.

Dim ctl As Control, Fltr As String
Dim Nam As String, fld As String

If Me.Dirty Then Me.Dirty = False 'Save first

For Each ctl In Me.Controls
If ctl.Tag = "?" Then
Nam = ctl.Name

If Trim(Me(Nam) & "") <> "" Then
fld = Switch(Nam = "Combo51", "[CustomerName] = '", _
Nam = "Combo59", "[enquiry_type] = '", _
Nam = "Combo61", "[enquiry_status] = '", _
Nam = "Combo63", "[Territory] = '", _
Nam = "Combo65", "[username] = '")
Call Command8_Click
If Fltr <> "" Then
Fltr = Fltr & " AND (" & fld & Me(Nam) & "')"
Else
Fltr = "(" & fld & Me(Nam) & "')"
End If
End If
End If
Next

If Fltr <> "" Then
Debug.Print Fltr
Me.Filter = Fltr
Me.FilterOn = True
Else
End If

i also have a separate filter which allows filtering between dates:


Me.Form.Filter = "[openingdate] BETWEEN #" & Format(Text4, "mmm dd,yyyy") & "# AND #" & Format(Text6, "mmm dd,yyyy") & "#"
Me.Form.FilterOn = True
Me.Form.OrderByOn = True

Could anyone help as to how i can combine so that if i filter to selected dates, it does not override any previous filters?? and vice versa....

Appreciate anyones help, tried to call the commands but they override any previous filter...

THanks

Ali

 

[Remou]

How about:

If Trim(Me.Filter & "")<>"" Then
    Me.Filter=Me.Filter & " AND " & NewFilterString
End If

 

[ali32j]

I used the following, but now it returns no results...

If Trim(Me.Filter & "") <> "" Then
Debug.Print Fltr
Me.Filter = Me.Filter & " AND " & Me.Filter = "[openingdate] BETWEEN #" & Format(Text4, "mmm dd,yyyy") & "# AND #" & Format(Text6, "mmm dd,yyyy") & "#"
Me.FilterOn = True
endif

Ali

 

[Remou]

Try:

strNewFilter="([openingdate] BETWEEN #" & Format(Text4, "yyyy/mm/dd") & "# AND #" & Format(Text6, "yyyy/mm/dd") & "#)"

   If Trim(Me.Filter & "") <> "" Then
      Debug.Print Fltr
        Me.Filter = Me.Filter & " AND " & strNewFilter
   Else
        Me.Filter = strNewFilter
   End If

Me.FilterOn = True

yyyy/mm/dd is a very safe format for dates, it is unambiguous.

 

[ali32j]

Sorry remou

No go, it does nt filter at all now...

Ali

 

[Remou]

When you switch to design view after filtering, what filter string appears in the filter property?

The notes above will only add a filter to an existing filter, you may wish to check if this same filter has already been applied, for example.