Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2010
    Posts
    5

    Unanswered: Query Criteria from Multiple List Boxes

    I'm trying to create a query where the criteria's are pulled from two list boxes. Right now, I've managed to query from the Vendor list box. I'm stuck now as I have no idea how to add a second criteria to the query by using the Deptartment# list box.

    I have attached my work-in-progress. Please take a quick look and any help is greatly appreciated.
    Attached Files Attached Files
    Last edited by riyiyi; 04-23-10 at 12:27.

  2. #2
    Join Date
    Apr 2010
    Posts
    5

    bump

    bumpbump!!

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    How do you have it working for the vendor listbox? That uses a hardcoded criteria in this query. If this is for a form/report, I use a technique like this:

    Multi-Select Listbox
    Paul

  4. #4
    Join Date
    Apr 2010
    Posts
    5
    Your code is a lot simpler and I like it!

    Now what if you wanted to create a second list box and use the selection as the 2nd criteria for the query? That is where I am stuck.

    I've added an additional field (State).
    Attached Files Attached Files

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Thanks! I have to leave for a bit so don't have time to look at your sample, but basically you would simply build a second string like the first. You want to build a string that ends up looking like:

    Field1 IN(...) AND Field2 IN(...)

    Post back if you're still stuck and I'll look at it when I come back.
    Paul

  6. #6
    Join Date
    Apr 2010
    Posts
    5
    Hey Paul,

    As I'm trying out your suggestion, I'm starting to see a problem. I think your sample application would only work if each line in the table is unique.

    On the other hand, say if there are 10 entries with last name of Doe in the table. 5 of them are from WI and 5 are from MN. In order to pull a report of employees with last name of Doe from MN, you would need to manually select all 5 Doe's in the list box instead of just selecting one. If you want to just select Doe and wish to pull reporting for all employees that have a last name of Doe, then you must use a query.

    Does that make sense? See attached zip.

    Thanks again for your help and suggestions. Really appreciate it.
    Attached Files Attached Files

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Well, if you're looking for all the Doe's, I might do it differently. Let's nail down exactly how you want to use this. Do you want listbox selections to find the specified person, or anybody with that name? Maybe you want to add a textbox or combo to find anybody with that name?
    Paul

  8. #8
    Join Date
    Apr 2010
    Posts
    5
    I want the first listbox to look for the last name(s) I selected. That selection would be the 1st criteria. So if I select "Doe", then any personw with last name of Doe would be included.

    Then I want the second listbox to look for the state(s) I selected. That would then be the 2nd criteria.

    Upon clicking the command button, a report or query would generate using those two criterias.

    See updated zip file.
    Attached Files Attached Files

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Then I would change the source of that listbox to display one instance of each name (and not include the ID), and use the text version I posted. Your SQL would end up looking like:

    LName In ('Doe') AND...
    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
  •