Page 1 of 4 123 ... LastLast
Results 1 to 15 of 51
  1. #1
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1

    Unanswered: Build Query Using Multi ListFilter Boxes

    I have a form with two listfilter boxes on it. One for Release and one for Layer.

    Currently I can make multiple selections from one listfilter and then run the report for those. Now I would like to add the second listfilter selections, to further refine the data.

    Current code for the working one list selection is below. Do I just need to repeat the routine before the End if, referencing the second listfilter name?

    Also (and yes I hate me too, I worked through this working solution with an excellent user from this site). Q: What do the Dims really do?

    Private Function GetCriteria() As String
    Dim stDocCriteria As String
    Dim VarItm As Variant
    For Each VarItm In ListFilter.ItemsSelected
    stDocCriteria = stDocCriteria & "[Release] = '" & ListFilter.Column(0, VarItm) & "' OR "
    Next
    If stDocCriteria <> "" Then
    stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) - 4)
    Else
    stDocCriteria = "True"
    End If
    GetCriteria = stDocCriteria
    End Function

    I then have the following code assigned to the RUN button on the form:

    Private Sub ButtonOpen_Click()
    DoCmd.OpenReport "Instances By Release", acViewReport, , GetCriteria()
    End Sub

    Any help very welcome!
    Last edited by Raddle; 12-30-10 at 09:25.

  2. #2
    Join Date
    Dec 2010
    Posts
    31

    factor emiseing

    Yes, you could loop through the other list right after the first, just "AND" them together so you end up with:

    (([Release] = item1 OR [Release] = item2) AND ([Layer] = item3 OR [Layer] = item4))

    Make sense?

  3. #3
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1
    This is what I think I am trying to go for based on what you were saying.

    stDocCriteria = stDocCriteria & "[Release] = '" & ListFilter.Column(0, VarItm) & "' OR " And "[Layer] = '" & ListFilter_1.Column(0, VarItm) & "' OR "

    This is all one line. Is that right?

    So many thanks for your help !!

    OK so this is where I am up to now...

    stDocCriteria = stDocCriteria & (("[Release] = '" & ListFilter.Column(0, VarItm) & "' OR ") And ("[Arch Layer] = '" & ListFilter_1.Column(0, VarItm) & "' OR "))

    What am I missing?
    Last edited by Raddle; 12-30-10 at 17:22.

  4. #4
    Join Date
    Dec 2010
    Posts
    31
    Since the list boxes are multi-select, you will have to loop through all of the items that were selected. Here is how I might write the function. Of course, you'll have to change the names of the controls...

    Private Function GetCriteria() As String
    Dim stDocCriteria As String
    Dim stFilter1 As String
    Dim stFilter2 As String
    Dim VarItm As Variant

    For Each VarItm In ListFilter.ItemsSelected
    stFilter1 = stFilter1 & "([Release] = '" & ListFilter.Column(0, VarItm) & "' OR "
    Next
    If stFilter1 <> "" Then
    stFilter1 = Left(stFilter1, Len(stFilter1) - 4) & ")"
    End If

    For Each VarItm In SecondListFilter.ItemsSelected
    stFilter2 = stFilter2 & "([Layer] = '" & SecondListFilter.Column(0, VarItm) & "' OR "
    Next
    If stFilter2 <> "" Then
    stFilter2 = Left(stFilter2, Len(stFilter2) - 4) & ")"
    End If

    If stFilter1 <> "" And stFilter2 <> "" Then
    stDocCriteria = "(" & stFilter1 & " AND " & stFilter2 & ")"
    ElseIf stFilter1 = "" And stFilter2 = "" Then
    stDocCriteria = ""
    Else
    stDocCriteria = IIf(stFilter1 <> "", stFilter1, stFilter2)
    End If

    GetCriteria = stDocCriteria
    End Function

    Let me know if that works for you.

  5. #5
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1
    Literally ... WOW!

    The finished product.

    Private Function GetCriteria() As String
    Dim stDocCriteria As String
    Dim stFilter1 As String
    Dim stFilter2 As String
    Dim VarItm As Variant

    For Each VarItm In ListFilter.ItemsSelected
    stFilter1 = stFilter1 & "([Release] = '" & ListFilter.Column(0, VarItm) & "' OR "
    Next
    If stFilter1 <> "" Then
    stFilter1 = Left(stFilter1, Len(stFilter1) - 4) & ")"
    End If

    For Each VarItm In ListFilter_1.ItemsSelected
    stFilter2 = stFilter2 & "([Layer] = '" & ListFilter_1.Column(0, VarItm) & "' OR "
    Next
    If stFilter2 <> "" Then
    stFilter2 = Left(stFilter2, Len(stFilter2) - 4) & ")"
    End If

    If stFilter1 <> "" And stFilter2 <> "" Then
    stDocCriteria = "(" & stFilter1 & " AND " & stFilter2 & ")"
    ElseIf stFilter1 = "" And stFilter2 = "" Then
    stDocCriteria = ""
    Else
    stDocCriteria = IIf(stFilter1 <> "", stFilter1, stFilter2)
    End If

    GetCriteria = stDocCriteria
    End Function

    Can't thank you enough.

    Have a helluva Happy New Year. THANK YOU!!


  6. #6
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1
    Been playing with this and admiring it all day and my last was in no way enough thanks.

    Really do appreciate your clear effort and skill, in helping me Mr R Ticky. You are clearly a wizard!!

    I do really hope we meet again!

    Thanks

    R

  7. #7
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1
    Hi - I may not have done all the checks that I should have before...

    When I make more than one selection per criteria, I do get an error which is challenging the syntax.

    Run-time error '3075'

    Missing ), ], or Item in query expresssion '(([Release = '0.0') AND ([Layer] = 'MTH' OR ([Layer] = 'NWA'))'

    However if I only choose one from each of the Release or Layer categories, is runs through fine.

    When it errors and I hit Debug, the editor highlights the following

    Private Sub ButtonOpen_Click()
    DoCmd.OpenReport "rptInstancesByLayerAndRelease", acViewReport, , GetCriteria()
    End Sub

    I don't think there are any errors with the report or underlying query, as they run fine, on their own, without these routine.

    Any hints on this last section?

    Thanks!

  8. #8
    Join Date
    Dec 2010
    Posts
    31
    Looks like a missing parenthesis. Sorry about that.

    Private Function GetCriteria() As String
    Dim stDocCriteria As String
    Dim stFilter1 As String
    Dim stFilter2 As String
    Dim VarItm As Variant

    For Each VarItm In ListFilter.ItemsSelected
    stFilter1 = stFilter1 & "([Release] = '" & ListFilter.Column(0, VarItm) & "') OR "
    Next
    If stFilter1 <> "" Then
    stFilter1 = Left(stFilter1, Len(stFilter1) - 4) & ")"
    End If

    For Each VarItm In SecondListFilter.ItemsSelected
    stFilter2 = stFilter2 & "([Layer] = '" & SecondListFilter.Column(0, VarItm) & "') OR "
    Next
    If stFilter2 <> "" Then
    stFilter2 = Left(stFilter2, Len(stFilter2) - 4) & ")"
    End If

    If stFilter1 <> "" And stFilter2 <> "" Then
    stDocCriteria = "(" & stFilter1 & " AND " & stFilter2 & ")"
    ElseIf stFilter1 = "" And stFilter2 = "" Then
    stDocCriteria = ""
    Else
    stDocCriteria = IIf(stFilter1 <> "", stFilter1, stFilter2)
    End If

    GetCriteria = stDocCriteria
    End Function

  9. #9
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1
    Not at all my friend.

    Did you add it in?

    I am getting the same thing I am sorry to report.

    Option Compare Database


    Private Function GetCriteria() As String
    Dim stDocCriteria As String
    Dim stFilter1 As String
    Dim stFilter2 As String
    Dim VarItm As Variant

    For Each VarItm In ListFilter.ItemsSelected
    stFilter1 = stFilter1 & "([Release] = '" & ListFilter.Column(0, VarItm) & "') OR "
    Next
    If stFilter1 <> "" Then
    stFilter1 = Left(stFilter1, Len(stFilter1) - 4) & ")"
    End If

    For Each VarItm In ListFilter_1.ItemsSelected
    stFilter2 = stFilter2 & "([Layer] = '" & ListFilter_1.Column(0, VarItm) & "') OR "
    Next
    If stFilter2 <> "" Then
    stFilter2 = Left(stFilter2, Len(stFilter2) - 4) & ")"
    End If

    If stFilter1 <> "" And stFilter2 <> "" Then
    stDocCriteria = "(" & stFilter1 & " AND " & stFilter2 & ")"
    ElseIf stFilter1 = "" And stFilter2 = "" Then
    stDocCriteria = ""
    Else
    stDocCriteria = IIf(stFilter1 <> "", stFilter1, stFilter2)
    End If

    GetCriteria = stDocCriteria
    End Function


    Private Sub ButtonOpen_Click()
    DoCmd.OpenReport "rptInstancesByLayerAndRelease", acViewReport, , GetCriteria()
    End Sub

    It highlights the DoCmd line ...

  10. #10
    Join Date
    Dec 2010
    Posts
    31
    Put a breakpoint in the code and show me what is in stDocCriteria.

  11. #11
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1
    Ahem.... how do I do that Maestro?

    *blushes*

  12. #12
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Here's a little primer on debugging:

    Debugging
    Paul

  13. #13
    Join Date
    Dec 2010
    Posts
    31
    Give this a try and lemme know:

    Private Function GetCriteria() As String
    Dim stDocCriteria As String
    Dim stFilter1 As String
    Dim stFilter2 As String
    Dim VarItm As Variant

    For Each VarItm In ListFilter.ItemsSelected
    stFilter1 = stFilter1 & "([Release] = '" & ListFilter.Column(0, VarItm) & "') OR "
    Next
    If stFilter1 <> "" Then
    stFilter1 = Left(stFilter1, Len(stFilter1) - 4)
    End If

    For Each VarItm In ListFilter_1.ItemsSelected
    stFilter2 = stFilter2 & "([Layer] = '" & ListFilter_1.Column(0, VarItm) & "') OR "
    Next
    If stFilter2 <> "" Then
    stFilter2 = Left(stFilter2, Len(stFilter2) - 4)
    End If

    If stFilter1 <> "" And stFilter2 <> "" Then
    stDocCriteria = "(" & stFilter1 & " AND " & stFilter2 & ")"
    ElseIf stFilter1 = "" And stFilter2 = "" Then
    stDocCriteria = ""
    Else
    stDocCriteria = IIf(stFilter1 <> "", stFilter1, stFilter2)
    End If

    GetCriteria = stDocCriteria
    End Function

  14. #14
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1
    Fabulous RT ... thank you so much.

    And I also know how to put in breaks now too!

    he he

    Thanks again.

  15. #15
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1
    At the risk of pushing my luck here....

    If I want to keep adding criteria, the elelments of the routines to alter are where there are references to stFilterX...

    If stFilter1 <> "" And stFilter2 <> "" And stFilter3 <> "" Then
    stDocCriteria = "(" & stFilter1 & " AND " & stFilter2 & " AND " & stFilter3 & ")"
    ElseIf stFilter1 = "" And stFilter2 = "" And stFilter3 = "" Then
    stDocCriteria = ""
    Else
    stDocCriteria = IIf(stFilter1 <> "", stFilter1, stFilter2, stFilter3)
    End If

    Is that about right?

    The stDocCriteria = IIf(stFilter1 <> "", stFilter1, stFilter2, stFilter3) is erroring saying wrong number of arguments. Should it be?

    Yes, I am now addicted to this....
    Last edited by Raddle; 01-04-11 at 17:28.

Posting Permissions

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