Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    The WhereCondition argument of DoCmd.OpenReport (2002/SR-1)

    I have a form that limits the records returned by generating a Where Condition before opening a report.
    The code that I'm using is posed below.

    <pre>Private Sub cmdPreview_Click()
    On Error GoTo Err_cmdPreview_Click
    Dim strWhere As String
    Dim varItem As Variant

    For Each varItem In lstInstructor.ItemsSelected
    strWhere = strWhere & "InstructorID =" _
    & lstInstructor.Column(0, varItem) & " Or "
    Next varItem
    strWhere = Left(strWhere, Len(strWhere) - 4) ' Remove the last " Or "
    strWhere = strWhere & " AND " ' Continue the Where statement.

    For Each varItem In lstCourse.ItemsSelected
    strWhere = strWhere & "CourseID =" _
    & lstCourse.Column(0, varItem) & " Or "
    Next varItem
    strWhere = Left(strWhere, Len(strWhere) - 4) ' Remove the last " Or "
    strWhere = strWhere & " AND " ' Continue the Where statement.

    For Each varItem In lstQuestion.ItemsSelected
    strWhere = strWhere & "qryEvalUnion.QuestionNumber =" _
    & lstQuestion.Column(0, varItem) & " Or "
    Next varItem
    strWhere = Left(strWhere, Len(strWhere) - 4) ' Remove the last " Or "

    DoCmd.OpenReport "rptGrandTotals", acViewPreview, , strWhere

    Exit_cmdPreview_Click:
    Exit Sub

    Err_cmdPreview_Click:
    MsgBox Err.Description
    Resume Exit_cmdPreview_Click

    End Sub</pre>



    When I select one instructor from the listbox, one however many courses, and however
    many questions, the report works fine. Only shows the records that meet the Where Condition.
    As soon as I select multiple Instructors from the multi Instructor listbox, the report displays
    inconsistent results. The whereCondition that the code generates is listed below:

    InstructorID =3 Or InstructorID =1 AND CourseID =1 AND
    qryEvalUnion.QuestionNumber =1 Or qryEvalUnion.QuestionNumber =7

    Does anyone see a problem here?

    If I go into the query behind the report and input the criteria in design view
    it displays the correct records.

    Thanks for you help.

    Don

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: The WhereCondition argument of DoCmd.OpenReport (2002/SR-1)

    I believe that you will have to put round brackets into the WHERE clause otherwise I think it just gets confused.

    (InstructorID =3 Or InstructorID =1) AND (CourseID =1) AND
    (qryEvalUnion.QuestionNumber =1 Or qryEvalUnion.QuestionNumber =7)

    I hope this works.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: The WhereCondition argument of DoCmd.OpenReport (2002/SR-1)

    Works great Pat!! I tried that but must not have had the parentheses right.

    Thanks!!
    Don

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •