Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filter data using combobox (2003)

    I have a form ( ViewComplaintsBySelection ) which has a combobox. When I select an entry from the combo box it runs a query to select records. This works correctly until I leave the combobox blank. At this stage NO records are returned - I want to display all records if it is blank.

    The criteria for the forms is selecting data using the following:
    IIf(IsNull([Forms]![ViewComplaintsBySelection]![SelectProduct]),Null,[Forms]![ViewComplaintsBySelection]![SelectProduct])

    I've tried replacing the NULL in the middle of this query with:
    ""
    Like "*"
    <> "XXXXX"
    but nothing seems to allow the selection of all records via this.

    I've also tried using ="" instead of IsNull for the first part of the criteria with no joy.

    Any suggestions?

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

    Re: Filter data using combobox (2003)

    Set the Criteria as follows:

    [Forms]![ViewComplaintsBySelection]![SelectProduct] Or [Forms]![ViewComplaintsBySelection]![SelectProduct] Is Null

    When you save, close and reopen the query, Access will rearrange the criteria in two rows. This is normal behavior, it is not an error.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter data using combobox (2003)

    Thanks Hans - that part is now working, though when i went back to look at the criteria it only had one line saying [Forms]![ViewComplaintsBySelection]![SelectProduct]

    I'm now adding 3 other combo boxes, SelectCategory, SelectType and SelectComplaint

    I want to be able to use these along with the SelectProduct combo to filter the records. Any of these may or may not be null - when null it shouldn't filter anything, when not null it should be used as the filter. I tried just adding the same criteria, with the names changed, but obviously it requires more than this.

    Any thoughts?

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

    Re: Filter data using combobox (2003)

    I suspect that Access has added an extra column [Forms]![ViewComplaintsBySelection]![SelectProduct] to the query with Is Null in the second criteria line. Is that correct?

  5. #5
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter data using combobox (2003)

    Sorry Hans - I'd added a few columns already so didn't notice the extra column it had added.

    I continued the form with 3 extra columns for which I added the same criteria (names changed). When I tried to run this, it would only work correctly if I had selected a product from the first list I'd set the combo box for. I checked the query and found that it had added a matrix of additional selections when I'd added the criteria for the 3 extra columns, but the product box wasn't included in the bigger matrix. I removed all of the criteria and reapplied it to all 4 at once. This provided an even bigger matrix which then worked correctly. I't all makes perfect sense once someone has shown you how!

    Many thanks for you advice

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

    Re: Filter data using combobox (2003)

    Glad you found it. When adding multiple criteria, it's often easiest to remove the existing criteria and start from scratch, as you have done. Building the "matrix" yourself is also possible, but it's more difficult (and more work).

Posting Permissions

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