Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2009
    Posts
    39

    Unanswered: Multi value list box to filter report

    I have a form with a multi value list box and what I want is for the users to select multiple cities they want, click on a command button then a report opens up the selected cities data shown in the report. I'm having a terrible time with this. I have the list box row source set to SELECT distinct [district] FROM districtqry order by district asc;
    This works fine and this list box is populated with districts and when I click to run the report I get records of all districts not the selected districts.

    districtqry is my query and is the record source for my district report, and all the district data is in my "conference" table which has a district column. I'm having trouble writing my SQL query, am I suppose to use the IN operator? here's what I have for my where clause

    'where district in (districtqry)'

    any help is appreciated.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's a way to build the WHERE part:
    Code:
        Dim strSQL As String
        Dim varItem As Variant
    
        For Each varItem In Me.List0.ItemsSelected
            If Len(strSQL) > 0 Then strSQL = strSQL & ", "
            strSQL = strSQL & "'" & Me.List0.ItemData(varItem) & "'"
        Next varItem
        strSQL = " WHERE District IN ( " & strSQL & " ) "
    Have a nice day!

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I do this type of thing:

    http://www.baldyweb.com/multiselect.htm
    Paul

  4. #4
    Join Date
    Jul 2009
    Posts
    39
    Thank You pbaldy, the code and tutorial database worked perfectly. I do have one more question, how do you insert the selected values from the list into the report title?

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    One way would be to declare strWhere as a public variable in a standard module. That lets you access it from other modules, so in your report's open event you can pop it into a textbox. Depending on version you could also pass it in OpenArgs and grab it from there in the open event. That would probably be my first option, but only available starting in 2002 if memory serves.
    Paul

Posting Permissions

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