Assign dynamic values to Me.Filter

[lodhi1978]

Hi All,

I have created a bound form that contains 5 unbound fileds. I want to filter the data thru the values of unbound fields.

I wrote following code on a button "ON Click" event.

Private Sub PBApplyFilter_Click()
    
    Dim row As Variant
    Dim itemdata As String
    Dim ActiveFilter As String
    Dim DistrictFilter As String
    Dim PolicyTypeFilter As String
    Dim DateRangeFilter As String
    'Filter for Cancel Only
    
    If CheckActive < 0 Then
      ActiveFilter = "[ACTIVE] = " & "0"
      Me.Filter = "[ACTIVE] = " & "0"
      
    Else
      ActiveFilter = "[ACTIVE] Like " & "*"
      Me.Filter = "[ACTIVE] Like " & "-1"
    End If
  MsgBox (Me.Filter)
  
    'Filter for District
    
    If ComboDistricts = 0 Then
       DistrictFilter = "[DISTRICT] Like " & "#"
    Else
        DistrictFilter = "[DISTRICT] Like " & Me.ComboDistricts
    End If

    'Filter for Policy Ttypes
    
    For Each row In Me.ComboPolicyTypes.ItemsSelected
    itemdata = itemdata & Me.ComboPolicyTypes.itemdata(row) & ","
    'MsgBox Me.ComboPolicyTypes.itemdata(row)
    
    Next row
    
    If itemdata = "" Then
        PolicyTypeFilter = "[POLICY TYPE] LIKE " & "*"
    
    Else
    
        itemdata = Left$(itemdata, Len(itemdata) - 1)
        Me.Filter = "[POLICY TYPE] IN (" & itemdata & ")"
        PolicyTypeFilter = "[POLICY TYPE] IN (" & itemdata & ")"
    
    End If
 
    'Filter for Date
    If IsNull(Me.StartDate) Or IsNull(Me.EndDate) Then
        
        DateRangeFilter = "[DUE DATE] LIKE " & "*"
    
    Else
        DateRangeFilter = "[DATE DUE] Between #" & Me.StartDate & "# And #" & Me.EndDate & "#"
    End If
        MsgBox (ActiveFilter)
        Me.Filter = [ActiveFilter] & " AND " & [DistrictFilter] & " AND " & [PolicyTypeFilter] & " AND " & [DateRangeFilter]
        MsgBox (Me.Filter)
    
    Me.FilterOn = True
    Me.Requery
    
    
End Sub

But it returned error:

Runtime error: Syntax error (missing operator) in query expression, '[ACTIVE] like * AND [DISTRICT] Like # AND [POLICY TYPE] IN (1,2,3) AND [DATE DUE] Between #1/31/2014/# and #/4/30/2014#

Kindly note that DISTRICT, POLICY TYPE, ACTIVE are number fields.

Kindly advice what is wrong with this code.

 

[spikepl]

What is wrong is that you are trying to do too many things at the same time without ensuring that each building block works.

Write code for just one filter and make each little bit bit of that code work one step at a time. Write code for the second filter and make that work.. and so on. When each separate bit works combine the results by addign one filter at a time.

For debugging use the Debugging Tips from here: http://www.access-programmers.co.uk/forums/showthread.php?t=149429