Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2004
    Location
    OHIO
    Posts
    75

    Unhappy Unanswered: filtering reports with two list boxes in one form

    i have a report that i'm trying to filter with two list boxes. Both list boxes reside on the same form and i got the code below from the previous developer of the database. It only seems to work if the list box values have a unique or id related to them....or maybe i'm missing something. One listbox holds awards which has award ids and the other list box holds dates(yyyy). I cannot get the date list box to work, however i can filter the report based on the awards selected. If someone has some better code or a idea that would help tremendously!



    query for listbox values (awards):
    Code:
    SELECT DISTINCT qryAwards.AwardTypeID, qryAwards.AwardName 
    FROM qryAwards 
    GROUP BY qryAwards.AwardTypeID, qryAwards.AwardName;
    query for listbox values (date):
    Code:
    SELECT DISTINCT Format([DateReceived],"yyyy") AS [Date Received]
    FROM qryAwards
    GROUP BY Format([DateReceived],"yyyy");
    VB Code for Awards list box
    Code:
    Private Function GetCriteria() As String
        Dim stDocCriteria As String
        Dim VarItm As Variant
            For Each VarItm In AwardList.ItemsSelected
            stDocCriteria = stDocCriteria & "[AwardTypeID]= " & AwardList.Column(0, VarItm) & " OR "
        Next
            If stDocCriteria <> "" Then
            stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) - 4)
        Else
            stDocCriteria = "True"
        End If
        
        GetCriteria = stDocCriteria
    End Function
    
    Private Sub Command27_Click()
        DoCmd.OpenReport "rptAwards", acPreview, , GetCriteria()
    End Sub
    Last edited by ironchef; 01-14-04 at 01:03.

  2. #2
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Cool Re: filtering reports with two list boxes in one form

    Originally posted by ironchef
    i have a report that i'm trying to filter with two list boxes. Both list boxes reside on the same form and i got the code below from the previous developer of the database. It only seems to work if the list box values have a unique or id related to them....or maybe i'm missing something. One listbox holds awards which has award ids and the other list box holds dates(yyyy). I cannot get the date list box to work, however i can filter the report based on the awards selected. If someone has some better code or a idea that would help tremendously!



    query for listbox values (awards):
    Code:
    SELECT DISTINCT qryAwards.AwardTypeID, qryAwards.AwardName 
    FROM qryAwards 
    GROUP BY qryAwards.AwardTypeID, qryAwards.AwardName;
    query for listbox values (date):
    Code:
    SELECT DISTINCT Format([DateReceived],"yyyy") AS [Date Received]
    FROM qryAwards
    GROUP BY Format([DateReceived],"yyyy");
    VB Code for Awards list box
    Code:
    Private Function GetCriteria() As String
        Dim stDocCriteria As String
        Dim VarItm As Variant
            For Each VarItm In AwardList.ItemsSelected
            stDocCriteria = stDocCriteria & "[AwardTypeID]= " & AwardList.Column(0, VarItm) & " OR "
        Next
            If stDocCriteria <> "" Then
            stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) - 4)
        Else
            stDocCriteria = "True"
        End If
        
        GetCriteria = stDocCriteria
    End Function
    
    Private Sub Command27_Click()
        DoCmd.OpenReport "rptAwards", acPreview, , GetCriteria()
    End Sub
    How goes it Ironchef.....do me a favor...email me when you get this figured out. I have had no success at getting a form to filter the choice for the selected data and could use a hand with that. Let me know
    Bud

  3. #3
    Join Date
    Jan 2004
    Location
    OHIO
    Posts
    75

    I assume no one knows?

    So, I guess no one has ever had to create a form with two listboxes that filter report results? If anyone has anything, a snip of code, some ideas i would appreciate it!

    BAF

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: I assume no one knows?

    Originally posted by ironchef
    So, I guess no one has ever had to create a form with two listboxes that filter report results? If anyone has anything, a snip of code, some ideas i would appreciate it!

    BAF
    This will rock your world ...

    Code:
    Function ConstructFilter() As String
        Dim FilterString As String
        Dim HaveFrom As Boolean, HaveThru As Boolean
        
        FilterString = "( "
        If SearchOnGroup.Value <> 0 Then
            Select Case SearchOnGroup.Value
                Case 1  ' Project Title
                    FilterString = FilterString & "[Work Orders II].[Project Title]"
                    Select Case MatchGroupBox.Value
                        Case 1  ' Exact
                            FilterString = FilterString & "='" & SearchTxt.Value & "'"
                        Case 2  ' Partial
                            FilterString = FilterString & " LIKE '%" & SearchTxt.Value & "%'"
                    End Select
                Case 2  ' Work Order
                    FilterString = FilterString & "[Work Orders II].WorkOrderNumber"
                    Select Case MatchGroupBox.Value
                        Case 1  ' Exact
                            FilterString = FilterString & "='" & SearchTxt.Value & "'"
                        Case 2  ' Partial
                            FilterString = FilterString & " LIKE '%" & SearchTxt.Value & "%'"
                    End Select
                Case 3  ' Brand
                    FilterString = FilterString & "[Work Orders II].WorkOrderNumber LIKE '%" & BrandComboBox.Value & "%'"
                Case 4  ' Requestor
                    FilterString = FilterString & "[Work Orders II].OwnerID='" & OwnerComboBox.Value & "'"
                Case 5  ' Customer
                    FilterString = FilterString & "CustomerContacts.ContactID=" & CustomerComboBox.Value
                Case Else
                    MsgBox "Garbage"
            End Select
            FilterString = FilterString & " )"
        End If
        
        If OpenGroup.Value <> 0 Then
            If SearchOnGroup.Value <> 0 Then FilterString = FilterString & " AND ( "
            Select Case OpenGroup.Value
                Case 1  ' Open
                    FilterString = FilterString & "[Work Orders II].Closed=False"
                Case 2  ' Closed
                    FilterString = FilterString & "[Work Orders II].Closed=True"
                Case Else
                    MsgBox "Garbage"
            End Select
            FilterString = FilterString & " )"
        End If
        
        HaveFrom = False
        HaveThru = False
        If FromTxt.Value & "" <> "" Then HaveFrom = True
        If ThruTxt.Value & "" <> "" Then HaveThru = True
        If HaveFrom Or HaveThru Then
            FilterString = FilterString & " AND ( [Work Orders II]."
            If OpenGroup.Value = 2 Then
                FilterString = FilterString & "ClosingDate"
            Else
                FilterString = FilterString & "IssueDate"
            End If
        End If
        If HaveFrom And HaveThru Then
        FilterString = FilterString & " BETWEEN #" & FromTxt.Value & "# AND #" & ThruTxt.Value & "#"
        Else
            If HaveFrom Then FilterString = FilterString & "=#" & FromTxt.Value & "#"
            If HaveThru Then FilterString = FilterString & "=#" & ThruTxt.Value & "#"
        End If
        If HaveFrom Or HaveThru Then FilterString = FilterString & " )"
        
        ConstructFilter = FilterString
    End Function
    This is the type of filtering that can be done ...

  5. #5
    Join Date
    Jan 2004
    Location
    OHIO
    Posts
    75

    wanted to thank you!

    Thanks for the code, unfortunately i have some questions. One what I assume that you have an application setup already that does filter a report with 2 list boxes. I was wondering if i could get a better look at the table structure and where the codes and and what tables/queries its related too. if you don't want to do that then some explanation of the code would help an amature like myself, make it work for my project.

    Again thanks a lot for the code. I trying to work through it. How many list boxes do you have and how are the cases working?

    BAF
    Last edited by ironchef; 01-16-04 at 14:50.

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: wanted to thank you!

    Originally posted by ironchef
    Thanks for the code, unfortunately i have some questions. One what I assume that you have an application setup already that does filter a report with 2 list boxes. I was wondering if i could get a better look at the table structure and where the codes and and what tables/queries its related too. if you don't want to do that then some explanation of the code would help an amature like myself, make it work for my project.

    Again thanks a lot for the code. I trying to work through it. How many list boxes do you have and how are the cases working?

    BAF
    Question back at you ... The listboxes you have, are they multi-select?

    Now, this is just how I do my programming ... I usually have the report query for it's own data for the most part. What I do is tie a report to a specific form and have a textbox on that form for parameters that the report will pull in order to retrieve the required data.
    Last edited by M Owen; 01-16-04 at 15:00.

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Here's some code that shows how to retrieve multiple items from a multi-select listbox ...

    Code:
    Private Sub RemoveButton_Click()
        If BreakDownList.ItemsSelected.Count < 1 Then Exit Sub
        
        Dim i As Long, BDRow As Variant, SelArray() As Integer
        
        i = 1
        ReDim SelArray(BreakDownList.ItemsSelected.Count)
        For Each BDRow In BreakDownList.ItemsSelected
            AvailQty = AvailQty + CDbl(BreakDownList.Column(1, BDRow))
            SQLString = "UPDATE " & WorkOrderFile & " SET IsVisible=True WHERE ( WorkOrderNumber='" & BreakDownList.Column(0, BDRow) & "' );"
            DoCmd.SetWarnings False
            DoCmd.RunSQL SQLString, False
            SelArray(i) = BDRow
            i = i + 1
        Next BDRow
        
        For i = BreakDownList.ItemsSelected.Count To 1 Step -1
            SQLString = "DELETE FROM " & BreakDownFile & " WHERE ( WorkOrderNumber='" & BreakDownList.Column(0, SelArray(i)) & "');"
            DoCmd.SetWarnings False
            DoCmd.RunSQL SQLString, False
        Next
        BreakDownList.Requery
            
        AvailQtyLbl.Caption = AvailQty
        WO_ComboBox.Requery
    End Sub

  8. #8
    Join Date
    Jan 2004
    Location
    OHIO
    Posts
    75

    Re: wanted to thank you!

    Originally posted by M Owen
    Question back at you ... The listboxes you have, are they multi-select?
    yes, they are multi-select!

  9. #9
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: wanted to thank you!

    Originally posted by ironchef
    yes, they are multi-select!
    Ok. Now, the item(s) selected are to OR'd or AND'd (from the 2 listboxes) for your filter?

  10. #10
    Join Date
    Jan 2004
    Location
    OHIO
    Posts
    75

    Re: wanted to thank you!

    Originally posted by M Owen
    Ok. Now, the item(s) selected are to OR'd or AND'd (from the 2 listboxes) for your filter?
    -AND'd

    listbox1 is JobList
    listbox2 is MotivationStyleList
    Last edited by ironchef; 01-16-04 at 15:09.

  11. #11
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: wanted to thank you!

    Originally posted by ironchef
    -AND'd

    listbox1 is JobList
    listbox2 is MotivationStyleList
    Ok. So what you'll want to do is iterate thru each listbox (or array if you so desire) and build each part of the filter clause and then AND them together and do your query.

    NOTE: You can iterate thru a listbox using the .Selected property and the .Index property ...

  12. #12
    Join Date
    Jan 2004
    Location
    OHIO
    Posts
    75

    cases

    so, how do i determine my "cases"
    i.e. case 1, case 2 . . . and what is the extended and partial in your example relating too?

    and does the filter code with all the cases, does that go into a module?

    I'm lost.... is there anyway you could give me a walkthrough of ur code?

    thanks,

    ba
    Last edited by ironchef; 01-18-04 at 23:11.

Posting Permissions

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