Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Apr 2008
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Print report using two criteria (2002)

    I am trying to print a report using select case code where there are two criteria from two different fields that will determine what prints.

    I tried this code

    DoCmd.OpenReport "rptDataForSelectedWeek", acViewPreview, , "Grade In ('6','7','8')" And "[Week] = Forms!frmSelectWeekToPrintData.ListBoxSelectWeek"

    The first criteria is grades 6, 7, 8 and the second part I tried to add is taking the data or dates shown in the listbox on the form as criteria. Can I do this or what's a better way.

    Thanks

    Paul

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Print report using two criteria (2002)

    The And should be within the quoted string:
    <code>
    DoCmd.OpenReport "rptDataForSelectedWeek", acViewPreview, , "Grade In ('6','7','8') And [Week] = Forms!frmSelectWeekToPrintData.ListBoxSelectWeek"
    </code>
    This will work if the list box is single-select. If it is a multi-select list box, the code is a bit more complicated - you have to loop through the selected items.

  3. #3
    2 Star Lounger
    Join Date
    Apr 2008
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print report using two criteria (2002)

    The new code did get rid of the error message, but it does not work when I select one week.

    The listbox on the form has multiselect capability. Is that the problem now? Is the loop code making this too complicated?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Print report using two criteria (2002)

    You can do it like this:
    <code>
    Dim strWeeks As String
    Dim strWhere As String
    Dim v As Variant

    strWhere = "Grade In ('6','7','8')"

    For Each v In Me.ListBoxSelectWeek.ItemsSelected
    strWeeks = strWeeks & ", " & Me.ListBoxSelectWeek.ItemData(v)
    Next v

    If Not strWeeks = "" Then
    strWhere = strWhere & " And [Week] In (" & Mid(strWeeks, 3) & ")"
    End If

    DoCmd.OpenReport "rptDataForSelectedWeek", acViewPreview, , strWhere
    </code>
    I have assumed that Week is a number field. If it is a text field, change the line
    <code>
    strWeeks = strWeeks & ", " & Me.ListBoxSelectWeek.ItemData(v)
    </code>
    to
    <code>
    strWeeks = strWeeks & ", " & Chr(34) & Me.ListBoxSelectWeek.ItemData(v) & Chr(34)
    </code>
    Chr(34) is the double quote character ".

  5. #5
    2 Star Lounger
    Join Date
    Apr 2008
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print report using two criteria (2002)

    Thanks Hans

    The new code works great!

Posting Permissions

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