asked on

Ms Access Filter a report

Hi,

I am trying to filter a report based on a series of combo boxes, text boxes and list boxes similar what I have seen on this site. My problem is that my test boxes for dates and combo boxes for Employees work fine. The filter looks up the employee name via an employee id in a query. The filter is applied to the report correctly for both the employee and dates. But, my combo box for a client text field in the query is not applied and the report opens with no filter applied for the client and returns the entire report. I have created a Filter function that I researched on this site. Any idea why the text filter is not applied Here is part of the filter function code:

Dim Where As Variant
Dim Item As Variant
Dim List As Variant
Dim strWhere As String
Dim lngLen As Long

Where = Null

If Not IsNull(Me.cboClient) Then
strWhere = strWhere & "([ChangedField] = """ & Me.cboClient & """)"
End If

If Not IsNull(cboEmployee) Then _
Where = Where + " AND " _
& "HabUnitID=" & cboEmployee

ASKER CERTIFIED SOLUTION

ChloesDad

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

TonyMannella

ASKER

Thank you for your response. I will try this tomorrow and let you know the results

TonyMannella

ASKER

Thank you for the suggestions. After using your example which works perfectly, I was told by my co-workers they would want a list box instead of a combo box for the Changed Field which is a Text Field in the table and query generating the report. The combo box and date field on the form work as expected, The code I am using is as follows. When I select one of the selected Items I get an error message which I have attached,

Not sure if I need to close this question out and score it or if I can look for an answer to the changed requirement. The code I am using follows (it is part of a Build Criteria Function.

With lstChanged
If .ItemsSelected.Count Then
List = Null
For Each Item In .ItemsSelected
List = List + "," & .ItemData(Item)
Next Item
Where = Where + " AND " _
& "[ChangedField] In (" & List & ")"
Filter-Error-Message.jpg

ChloesDad

Set List = "" rather than List = NULL.

This is because NULL + something = NULL

TonyMannella

ASKER

Thank you for the suggestion. However when I used Set List = "", I get a compile error indicating an object is required and the double quotes are highlighted.

Also, I'm wondering if I have to use some kind of text string like: & "[ChangedField] = " & chr$(34) & me.cboClient & chr$(34), but since I am relativelyu new to this aspect of VBA programming, I am not sure how to code it for a text field in the query/table. Any help wpuld be appreciated.

Thank you

ChloesDad

Sorry, not Set List = "", just List = "".

As its a string you don't need the Set, that is just for Objects

And yes you should add quotes around the string. You can use " or '

TonyMannella

ASKER

Hi,

Made the change but still getting an error message which I have attached. Would it make sense to provide a "stripped down version of the db with the specified objects?

or is this enough info?

ChloesDad

The error is in

List = List + "," & .ItemData(Item)

Item is already the selected item, so you probably need

List = List + "," & Item.tostring

You can put a break point at this line to confirm that Item.Tostring is giving you the field name.

Also, you will always add a leading comma, which is not required so add code to only add the leading comma if list <> ""

e.g.

For Each Item In .ItemsSelected
IF List <> "" THEN
List = List & "," & Item.Tostring
else
List = List & item.Tostring
End if
Next Item

TonyMannella

ASKER

Thanks I'll give it a try.

TonyMannella

ASKER

Sorry, that didn't work either. Received an Object not defined error. I attached a redacted version of the database with the form. The form was from a demo database here.

Any help with the list box behaving correctly would be greatly appreciated
Database3.accdb

ChloesDad

You have errors in two routines

In BuildFilter when building the list, each name should be enclosed in quotes, and the list should not start with a leading comma. I also changed it to read the column value for each item.

Public Function BuildFilter()
' each control is examined in turn and used to add a predicate to the filter
    
    Dim Where As Variant
    Dim Item As Variant
    Dim List As Variant
    Dim strWhere As String
    Dim lngLen As Long

    
    Where = Null
    
'If Not IsNull(Me.cboClient) Then
        'strWhere = strWhere & "([ChangedField] = """ & Me.cboClient & """) AND "
'End If

    If Not IsNull(cboEmployee) Then _
        Where = Where + " AND " _
            & "HabUnitID=" & cboEmployee
    
    With lstChanged
        If .ItemsSelected.Count Then
            List = ""
            For Each Item In .ItemsSelected
              If List = "" Then
                List = List + "'" & .Column(0, Item) & "'"
              Else
                List = List + " , '" & .Column(0, Item) & "'"
              End If
              
            Next Item
            Where = Where + " AND " _
                & "[ChangedField] In (" & List & ")"
        End If
    End With
    
    If IsNull(txtFromDate) Then
        If IsNull(txtToDate) Then
            ' no filter...
        Else
            Where = Where + " AND " _
                & "zTimestamp<=" & DateSQL(txtToDate)
        End If
    Else
        If IsNull(txtToDate) Then
            Where = Where + " AND " _
                & "zTimestamp>=" & DateSQL(txtFromDate)
        Else
            Where = Where + " AND " _
                & "zTimestamp Between " & DateSQL(txtFromDate) _
                & " And " & DateSQL(txtToDate)
        End If
    End If
    'If Not IsNull(cboProduct) Then _
        'Where = Where + " AND " _
            '& "OrderID In (" _
            '& "  Select OrderID" _
            '& "  From [Order Details]" _
            '& "  Where ProductID=" & cboProduct _
            '& "  )"
            
    'If Not IsNull(cboCategory) Then _
       ' Where = Where + " AND " _
            '& "OrderID In (" _
           ' & "  Select D.OrderID" _
           ' & "  From" _
            '& "    [Order Details] D Inner Join" _
            '& "    Products P On D.ProductID=P.ProductID" _
            '& "  Where P.CategoryID=" & cboCategory _
            '& "  )"
    
    BuildFilter = Where

End Function

Open in new window

I also made similar changes in ExplainFilter

Public Function ExplainFilter()
' similar to BuildCriteria, but generating a human-readable string
    
    Dim Criteria As Variant
    Dim Item As Variant
    Dim List As Variant
    Dim strWhere As String
    Dim lngLen As Long
   
    Criteria = ""
    
  'If Not IsNull(Me.cboClient) Then
        'strWhere = strWhere & "([ChangedField] = """ & Me.cboClient & """) AND "
'End If
    
    If Not IsNull(cboEmployee) Then _
        Criteria = Criteria + " • " _
            & "Employee: " & cboEmployee.Column(2)
    
    With lstChanged
        If .ItemsSelected.Count Then
            List = ""
            For Each Item In .ItemsSelected
                If List <> "" Then
                  List = List & "," & .Column(0, Item)
                Else
                  List = List & "ChangedField: " & .Column(0, Item)
                End If
            Next Item
            Criteria = Criteria + " • " + List
                
        End If
    End With
    
    If IsNull(txtFromDate) Then
        If IsNull(txtToDate) Then
            ' no filter...
        Else
            Criteria = Criteria + " • " _
                & "Ordered up to " & txtToDate
        End If
    Else
        If IsNull(txtToDate) Then
            Criteria = Criteria + " • " _
                & "Ordered since " & txtFromDate
        Else
            Criteria = Criteria + " • " _
                & "Ordered between " & txtFromDate _
                & " and " & txtToDate
        End If
    End If
      
    ExplainFilter = Criteria
   
End Function

Open in new window

This now gives me the correct results

SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

TonyMannella

ASKER

Both Solutions worked very well. The primary solution captured everything I was looking for, while the secondary solution (Building a List filter) to building a list can be used as an alternative.