none
DoCmd.OpenReport Where Condition Issue

    Question

  • Hey,

    I'm fairly new to Access and I'm trying to use DoCmd.OpenReport in a function which I have declared as such:

    DoCmd.OpenReport "x Comment -rpt", _
                View:=acViewPreview, _
                WhereCondition:=strCriteria

    The problem is with my WhereCondition. I'm basically entering a string in the format "[EmployeeNumber] In(12345,56789)" this doesn't seem to be working. When I run the code and it opens the report the output I'm recieving doesn't match what I'm looking for (The people who's Employee Numbers are 12345 and 56789). I'm assuming it's the way I declared the string because I have the same issue when I enter it as an expression in my query. Does anyone know what's going on?


    One must therefore be a fox to recognize traps, and a lion to frighten wolves.
    Wednesday, March 30, 2011 11:49 AM

Answers

  • Ok some of my team members helped me fix the issue. It turned out that because EmployeeNumber for said query was not included in the report itself it was not being filtered correctly.
    One must therefore be a fox to recognize traps, and a lion to frighten wolves.
    • Marked as answer by Don Mon Thursday, March 31, 2011 12:08 PM
    Thursday, March 31, 2011 12:08 PM

All replies

  • After further evaluation of the situation I realized that if I put the following in my "feild" section of my query

    Expr1: ([x CC (Comments) -qry][EmployeeNumber] In("12345","56789"))

    And I uncheck the "show" checkbox and set the "criteria" to True this works.

    This leads me to believe that the expression criteria I'm putting in my WhereCondition does not try to evaluate the expression to see it it's True and hence does not filter my content. Is there a way i can get it to do this?


    One must therefore be a fox to recognize traps, and a lion to frighten wolves.
    Wednesday, March 30, 2011 12:36 PM
  • Hi,

    it means only that your EmployeeNumber is a text field rather than a number one. If you change you code a little it should work:

    strCriteria = "[EmployeeNumber] In('12345','56789')"
    


    Andrey V Artemyev | Saint-Petersburg, Russia
    Wednesday, March 30, 2011 1:16 PM
  • Ok I read somewhere that the OpenReport method can only filter by the columns in your select query I'll post the current code I have that executes on my buttons OnClick event. I'm still encountering the same problems all around. i.e. if I enter [EmployeeNumber] In('12345','56789') in my field of my query and True in the criteria the query executes correctly.

    Public Function btnGenx_OnClick()

        Dim var As Variant
        Dim employeeNumList As String
        Dim criteria As String
        Dim ctrl As Control
       
        Set ctrl = [Form_CC Manager Comments Access -qry].lboxx
        
        '**********
        ' Open the report based
        ' on the selected employees
        '**********
        If ctrl.ItemsSelected.Count > 0 Then
            For Each var In ctrl.ItemsSelected
                employeeNumList = employeeNumList & ",'" & ctrl.ItemData(var) & "'"
            Next var
           
            'Remove the leading comma
            employeeNumList = Mid(employeeNumList, 2)

            'Filter by the selected employee numbers
            criteria = "[EmployeeNumber] In(" & employeeNumList & ")"
           
            DoCmd.OpenReport "x Comment -rpt", acViewPreview, , criteria
        Else
            MsgBox "No Employees Selected", vbInformation, "Warning"
        End If


       
    End Function


    One must therefore be a fox to recognize traps, and a lion to frighten wolves.
    Wednesday, March 30, 2011 2:08 PM
  • Yes, the EmployeeNumber column must be returned as a column in the report's underlying query.  It doesn't need to be shown in the report, however.

    How is the list box set up?  Assuming you want to select by name rather than number, it should be something like this:

    RowSource:     SELECT EmployeeNumber, FirstName & " " & LastName FROM Employees ORDER BY LastName, FirstName;

    BoundColumn:   1
    ColumnCount:   2
    ColumnWidths:  0cm;8cm

    If your units of measurement are imperial rather than metric Access will automatically convert the last one.  The important thing is that the first dimension is zero to hide the first column and that the second is at least as wide as the control.  Whether or not you are showing the number in the list or not, however, the important thing is that it is the bound column.

    If you still have problems temporarily amend the code as follows:

            'Filter by the selected employee numbers
            criteria = "[EmployeeNumber] In(" & employeeNumList & ")"

            ' add this line
            Debug.Print criteria

            DoCmd.OpenReport "x Comment -rpt", acViewPreview, , criteria

    Then select some employees, click the button, and see what it says in the debug window (press Ctrl-G to open it).


    Ken Sheridan, Stafford, England
    Wednesday, March 30, 2011 5:27 PM
  • Hello Again Ken,

    Thank you for showing interest again. This is my current listbox setup.

    Row Source: SELECT [x CC (Submitted/Unique) -qry].[Employee Name], [x CC (Submitted/Unique) -qry].[EmployeeNumber] FROM [x CC (Submitted/Unique) -qry] GROUP BY [EmployeeNumber], [Employee Name] ORDER BY [Employee Name];

    Bound Column: 2

    Column Count: 2

    Column Widths: 1";0"

    When I run the code as you request the Debug window displays the following:

    [EmployeeNumber] In('27463','10957','11339')


    One must therefore be a fox to recognize traps, and a lion to frighten wolves.
    Wednesday, March 30, 2011 5:40 PM
  • There seems to be a lot of documentation of problems with this WhereCondition of OpenReport all over the web but not a lot of answers. It seems like a pretty regular use solution for multi select listboxes. I wonder why it's not better documented,
    One must therefore be a fox to recognize traps, and a lion to frighten wolves.
    Wednesday, March 30, 2011 6:35 PM
  • It appears as if the WhereCondition is just creating a seperate column that fills with 0's unless it matches the [EmployeeNumber] In('27463') statement in which case it returns -1.
    One must therefore be a fox to recognize traps, and a lion to frighten wolves.
    Wednesday, March 30, 2011 6:47 PM
  • That sounds more like a problem with the report's underlying query.  What is the SQL statement for that?
    Ken Sheridan, Stafford, England
    Wednesday, March 30, 2011 8:23 PM
  • SQL is as follows:

    SELECT

    [x (submitted/Unique) -qry].[Cost Center],

    [x CC (submitted/Unique) -qry].EmployeeNumber,

    [x CC (submitted/Unique) -qry].[Employee Name],

    [x CC (submitted/Unique) -qry].Category,

    [H5 Awards].[Award Reason],

    [H5 Awards].[Issue Date],

    [H5 Awards].Comments
    FROM

    [x CC (submitted/Unique) -qry]

    INNER JOIN [H5 Awards]

    ON [x CC (submitted/Unique) -qry].EmployeeNumber = [H5 Awards].[Issuer Employee #]
    WHERE

    (((Month([H5 Awards].[Issue Date]))=[Forms]![CC Manager Comments Access -qry]![ddMonth])

    AND ((Year([H5 Awards].[Issue Date]))=Year(Date())));

     


    One must therefore be a fox to recognize traps, and a lion to frighten wolves.
    Thursday, March 31, 2011 11:32 AM
  • I have found a small issue in what I was doing that I recently fixed but it has not remedied the situation. I had attached the query to a subreport within the report I was accessing. I have remedied that particular situation and now when I click the button to run the function a 'Enter Parameter Value' box pops up prompting for the EmployeeNumber. This is obviously not what it's supposed to be doing and when I type the employee number in it still does not filter it correctly.
    One must therefore be a fox to recognize traps, and a lion to frighten wolves.
    Thursday, March 31, 2011 11:42 AM
  • I've also noticed that despite my code clearly stating:

    DoCmd.OpenReport "Thomas Fitzgibbon Comment -rpt", acViewPreview, , criteria

    The design view of the report keeps filling in the criteria in the "Filter" section under the Data tab. I wasn't sure if it should be doing this when criteria is under the Where Condition and not the Filter Name.


    One must therefore be a fox to recognize traps, and a lion to frighten wolves.
    Thursday, March 31, 2011 11:50 AM
  • Ok some of my team members helped me fix the issue. It turned out that because EmployeeNumber for said query was not included in the report itself it was not being filtered correctly.
    One must therefore be a fox to recognize traps, and a lion to frighten wolves.
    • Marked as answer by Don Mon Thursday, March 31, 2011 12:08 PM
    Thursday, March 31, 2011 12:08 PM