Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Nov 2001
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ComboBox to 'search for any or all' (Access97)

    I have a form that displays a table of "orders" that has a field for "customer". I want to add a filter with a combo-box listing all the customers, so that the filter displays all the orders for the selected customer. I can do this by populating the combo-box from the "customers" table or a query that extracts them from the "orders" table. And I can turn the combo-box selection into a "WHERE" clause for a query or a filter.

    But what I'd like is an additional option in the combo-box for "all customers", and so far I've only thought of slightly clunky ways to do this, with a fake entry for "all customers" in the customers table, and code in one of the combo-boxes events that checks for this being chosen, and generates a different query or filter as a special case. Has anyone come up with a neater way to do this? Many databases seem to have this (including some of the Access Wizards), where you get a list of, say, countries, and the first entry is "all countries", so it must be a frequently asked question. Also, the last time I tried this, the users couldn't tell whether they were choosing "customer = any" or "customer = null", which could be the case in this database because you're allowed to have incomplete entries.

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

    Re: ComboBox to 'search for any or all' (Access97)

    You don't really need a fake entry, you can create a union query to add an "All Customers" item:

    SELECT "(All Customers)" As Customer, 1 As SortBy FROM tblCustomers
    UNION
    SELECT Customer, 2 FROM tblCustomers
    ORDER BY SortBy

    You will still need to handle the situation that the user selects the "All Customers" item separately.

Posting Permissions

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