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
TonyMannellaBusiness/IT AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ChloesDadCommented:
If you are searching for a text string then the string should be enclosed in single quotes 'xxxx' not double quotes "xxxx" and you should use me.cboclient.selecteditem.tostring as this will be the text. me.cboclient will be the combobox.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeCommented:
I would disagree with ChloesDad regarding single or double quotes; either will work in Access as long as the SQL string is formatted properly.

The code you provided is either incomplete or flawed.  I'm not sure why you are using both a variant variable (WHERE) and a string variable (strWhere).  The general syntax I use to build this type of multiple text/combo criteria string looks something like:
Private Sub cmd_Report_Click

    Dim varWhere As Variant  'use the prefix to make sure you know the type of variable

    varWhere = Null

    if Len(me.txtDate & "") > 0 Then
        varWhere = "[DateField] = #" & me.txtDate & "#"
    end if

    if Len(me.cbo_Employee & "") > 0 then
        'Assumes [HabUnitID] field and cboEmployee bound column are numeric
        varWhere = (varWhere + " AND ") _
                & "[HabUnitID] =" & me.cboEmployee
    end if

    If LEN(Me.cboClient & "")  > 0 Then
        varWhere = (varWhere + " AND ") _
                & "[ChangedField] = " & chr$(34) & me.cboClient & chr$(34)
    End If

    Docmd.OpenReport "myReport", acViewPreview,,varWhere, acDialog

End Sub

Open in new window

This syntax is the easiest to read when embedding a text value in a SQL string.  There are other techniques, there are 3 " before me.cboClient and 4 after

        varWhere = (varWhere + " AND ") _
                          & "[ChangedField] = """ & me.cboClient & """"

But that is not as easy to read.  I'm lazy, so fewer keystrokes and ease of reading is important to me.  I actually use a function (see below) so that I can use this syntax:

        varWhere = (varWhere + " AND ") _
                          & "[ChangedField] = " & fnQuotes(me.cboClient)
Private Function fnQuotes(QuoteWhat as Variant, Optional Delimiter as string = """") as Variant

    If isnull(QuoteWhat) then
        fnQuotes = NULL
    Else
         fnQuotes = Delimeter _
                  & Replace(QuoteWhat, Delimeter, Delimeter & Delimeter) _
                  & Delimeter
    End If

End Function

Open in new window

I can pass either a single quote (for use with SQL Server pass through queries), double quote, or even a # to wrap date values.
0
TonyMannellaBusiness/IT AnalystAuthor Commented:
Thank you for your response.  I will try this tomorrow and let you know the results
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

TonyMannellaBusiness/IT AnalystAuthor Commented:
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
0
ChloesDadCommented:
Set List = "" rather than List = NULL.

This is because NULL + something = NULL
0
TonyMannellaBusiness/IT AnalystAuthor Commented:
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
0
ChloesDadCommented:
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 '
0
TonyMannellaBusiness/IT AnalystAuthor Commented:
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?New Error Message  or is this enough info?
0
ChloesDadCommented:
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
0
TonyMannellaBusiness/IT AnalystAuthor Commented:
Thanks I'll give it a try.
0
TonyMannellaBusiness/IT AnalystAuthor Commented:
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
0
ChloesDadCommented:
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
0
Dale FyeCommented:
Personally, I use a function I wrote a while back that gives me some flexibility to address all types of lists.

In your case, I would replace the cboClient code block in my previous post with
    If LEN(Me.cboClient & "")  > 0 Then
        varWhere = (varWhere + " AND ") _
                & "[ChangedField] " & fnMultiList(me.lstChanged)
    End If

Open in new window

If no items are selected in the list, the function returns "Is NOT NULL", which means that your filter would include all records where [ChangedField] has a value.  Otherwise, the function will return "= X" if only one item is selected or " IN (X, Y, Z) if three items are selected.  It will also wrap those values if the bound column (or alternately the UseColumn) in quotes if that field is a string.

Public Function fnMultiList(lst As ListBox, Optional SelectAll As Boolean = False, _
                            Optional UseColumn As Integer = -1) As Variant

    Dim varItem As Variant
    Dim lngItem As Long
    Dim strDelimiter As String
    
    fnMultiList = Null
    
    'Determine how to delimit the list items
    If UseColumn = -1 Then UseColumn = lst.BoundColumn - 1
    If IsNumeric(lst.Column(UseColumn, Abs(lst.ColumnHeads))) Then
        strDelimiter = ""
    Else
        strDelimiter = Chr$(34)
    End If
    
    'loop through the selected items in the list
    If lst.MultiSelect = 0 And SelectAll = False Then
        fnMultiList = lst.Value
    Else
        For lngItem = 0 To lst.ListCount
            If lst.Selected(lngItem) = True Or SelectAll Then
                fnMultiList = (fnMultiList + ",") _
                            & strDelimiter & lst.Column(UseColumn, lngItem) _
                            & strDelimiter
            End If
        Next lngItem
    End If
    
    'Strip trailing "," if there is one
    If Right(fnMultiList, 1) = "," Then fnMultiList = Left(fnMultiList, Len(fnMultiList) - 1)
    
    'Depending on the number of items selected, determine how the "list values" are returned
    If Len(fnMultiList & "") = 0 Then
        fnMultiList = " IS NOT NULL"
    Else
        Select Case Len(fnMultiList) - Len(Replace(fnMultiList, ",", ""))
            Case 0
                fnMultiList = " = " & fnMultiList
            Case Else
                fnMultiList = " IN (" & fnMultiList & ")"
        End Select
    End If
    
End Function

Open in new window

0
TonyMannellaBusiness/IT AnalystAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.