report filter is not working

[pduncan]

I am trying to filter a report based on combobox selections. When a value is welected for a combobox, that value is passed to the query, but when a combobox value is not shosen, it is supposed to pass a "like '*'" but it isn't. Can someone please have a look at this code and tell me where I am going wrong? OR - if you have a better suggestion, please let me know.

Private Sub cmdApplyFilter_Click()

    Dim strController As String
    Dim strActivity As String
    Dim strLocation As String
    Dim strStartDate As String
    Dim strEndDate As String
    Dim strFilter As String
    Dim stDocName As String
    
    stDocName = "rptActivity"
    
    If SysCmd(acSysCmdGetObjectState, acReport, stDocName) <> acObjStateOpen Then
        MsgBox "You must have the report open first." & vbCrLf & "Press the 'Open Report' button", , "Notice"
        Exit Sub
    End If
    
'Build criteria string for Controller field
    If IsNull(Me!cboController.Value) Then
        strController = "Like '*'"
    Else
        strController = "='" & Me!cboController.Value & "'"
    End If

'Build criteria string for Activity field
    If IsNull(Me!cboActivity.Value) Then
        strActivity = "Like '*'"
    Else
        strActivity = "='" & Me!cboActivity.Value & "'"
    End If

'Build criteria string for Location field
    If IsNull(Me!cboLocation.Value) Then
        strLocation = "Like '*'"
    Else
        strLocation = "='" & Me!cboLocation.Value & "'"
    End If

'Build criteria string for Start Date field


'Build criteria string for End Date field

'Combine criteria strings into where clause for the filter
    strFilter = "Controller " & strController & " AND Event " & strActivity
    
    MsgBox strFilter
    
    With Reports![rptActivity]
        .Filter = strFilter
        .FilterOn = True
    End With

End Sub

Thanks, PDUNCAN
Memphis, TN - USA
When I die, I want to die like my grandfather-- who died peacefully in
his sleep. Not screaming like all the passengers in his car.

 

[PaulBricker]

Couple questions
1. What does your MsgBox print out?

2. What does your Open Button code do. You may need to set all these filters before you open the Report (not sure but it's worth checking).

Paul

 

[pduncan]

I meant to remove that - sorry. It's just a test I had to show me the values that were being placed into the strings.
The open button makes sure the report is open first. If a filter is applied, right now the report will be empty. Once I press the "remove filter" button, its fully populated. I have tried to create the filter with the report closed and it gave me the same result.

currently - when this is ran it creates a filter something like this:
Controller ='Andy' AND Event =''

Thanks, PDUNCAN
Memphis, TN - USA
When I die, I want to die like my grandfather-- who died peacefully in
his sleep. Not screaming like all the passengers in his car.

 

[PaulBricker]

The results of the Message Box is what I was interested in. It seems to me you should build the Filter string as you go.

If IsNull(Me!cboController.Value) Then
        Else
        strFilter = "Controller = '" & Me!cboController.Value & "'"
    End If

'Build criteria string for Activity field
    If IsNull(Me!cboActivity.Value) Then
        
    Else
        strFilter = strFilter & "
And" &  "Event = '" & Me!cboActivity.Value & "'"
    End If

'Build criteria string for Location field
    If IsNull(Me!cboLocation.Value) Then
        Else
        strFilter  = strFilter & "And" & "Location = '" & Me!cboLocation.Value & "'"
    End If

This way, if any are null you just skip it and go on to the next.  See if that helps.

Paul

 

[pduncan]

I see where you are going with this - but its not working. the complete filter string is just "controller and event"


Thanks, PDUNCAN
Memphis, TN - USA
When I die, I want to die like my grandfather-- who died peacefully in
his sleep. Not screaming like all the passengers in his car.

 

[PaulBricker]

So is the Event just the Activity combo box value?
What are you getting for your MsgBox value?


Paul