Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Jerusalem, Israel
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Display Filtered Data From Two Lists (Access 2000)

    I have a form that displays two lists (list A and list . The options that appear in list B display according to what option you choose in list A.
    I now want to produce a form that shows the results of only where the option chosen in list A and the option chosen in list B exist.

    Some time ago I found instructions somewhere that showed me how to produce a blank text box that is automatically populated with the choice entered into a list. Since I have two lists, I adapted the code this time so that when you click the "Search" button, it fills in two text boxes, one with the search option selected in list A (txtQueryCriteria) and the other with the search option selected in list B (txtQueryCriteria2).

    Now, here is the tricky part: I have a query on which my search results form is based. According to the instructions I have to enter the following into the query:

    Field: [Forms]![frm_searchstatuses]![txtQueryCriteria]
    Criteria: Like "*;" & [tbl_Sales]![Status] & ";*"

    (Along with the rest of the fields I want to display).

    This works very well for 1 listbox. However, I have no idea how to set up the query to also filter by the data entered automatically into txtQueryCriteria2, which is the option from list B. I have tried a few combinations, without success.

    I want to show results on the results form where BOTH the option selected in list A and list B are true (not one or the other).

    Thanks for your help!

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

    Re: Display Filtered Data From Two Lists (Access 2000)

    Since the options that appear in list B depend on the item selected by the user in list A, isn't it sufficient to look at list B only?
    If that is not the case, you'll have to provide much more detailed information.

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    Jerusalem, Israel
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display Filtered Data From Two Lists (Access 2000)

    Hans,

    Thanks for your help.

    Some options in list B are common to options in list A. For example, if list A contained names and list B contained sales regions, then John might be a salesperson in regions 1, 2 and 3, while Jim might be a salesperson in regions 1, 4 and 5 - so there is overlap. But I might want to find all records with John and region 1 (ie: excluding every other salesperson who might also operate in region 1).

    What additional information would I need to provide?

    JoeK

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

    Re: Display Filtered Data From Two Lists (Access 2000)

    Preferably a stripped down and zipped copy of the database (See <post#=401925>post 401925</post#> for instructions.)
    Otherwise, we'd need to know the names of the tables and the names and field types of the relevant fields, and the names of the relevant forms and controls.

  5. #5
    2 Star Lounger
    Join Date
    May 2002
    Location
    Jerusalem, Israel
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display Filtered Data From Two Lists (Access 2000)

    Hans,

    Hi. Since your last post, I spoke with a friend offline who told me to try doing a Union query. I tried it, but the resulting form filters only the first part of the Union and not the second. Here is the code - I had to modify the names of tables etc due to privacy issues:

    ===========

    SELECT tbl_MainTable.[Data1], tbl_MainTable.[Data2], tbl_MainTable.Status, tbl_MainTable.Substatus, tbl_MainTable.FirstCreated, [Forms]![frm_searchstatuses]![txtQueryCriteria] AS Expr1
    FROM tbl_MainTable
    WHERE ((([Forms]![frm_searchstatuses]![txtQueryCriteria]) Like "*;" & [tbl_Maintable]![Substatus] & ";*"))
    ORDER BY tbl_MainTable.[Data1];

    UNION SELECT tbl_MainTable.[Data1], tbl_MainTable.[Data2], tbl_MainTable.Status, tbl_MainTable.Substatus, tbl_Maintable.FirstCreated, [Forms]![frm_searchstatuses]![txtQueryCriteria2] AS Expr2
    FROM tbl_MainTable
    WHERE ((([Forms]![frm_searchstatuses]![txtQueryCriteria2]) Like "*;" & [tbl_Maintable]![Status] & ";*"))

    ===========

    So, for example, if in the first list box I choose Status 1 and Substatus A, the query displays only records with Status 1, but that have any Substatus.

    Perhaps you know a way to make the Union query display only those records that have Status 1 and Substatus A, but not any other status or substatus.

    (I was hoping to avoid all the work involved in stripping down my database).

    Thanks,

    JoeK

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

    Re: Display Filtered Data From Two Lists (Access 2000)

    How about

    SELECT Data1, Data2, Status, SubStatus, FirstCreated
    FROM tbl_MainTable
    WHERE [Forms]![frm_searchstatuses]![txtQueryCriteria] Like "*;" & [tbl_Maintable]![Substatus] & ";*" AND [Forms]![frm_searchstatuses]![txtQueryCriteria2] Like "*;" & [tbl_Maintable]![Status] & ";*"
    ORDER BY Data1

  7. #7
    2 Star Lounger
    Join Date
    May 2002
    Location
    Jerusalem, Israel
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display Filtered Data From Two Lists (Access 2000)

    Hans,

    I implemented the code you suggested and it works great! You made me look really good.

    Can't thank you enough. Here's a "bravo" smiley of appreciation: <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

    JoeK

Posting Permissions

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