Results 1 to 3 of 3
  1. #1
    leonardbd is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Mar 2015
    Posts
    3

    Access Combo box filter all records when All selected using expression builder


    Hello there,
    I am facing a issue filtering all records in query table when I select "All" from a combo box. Following image will explain all..



    Click image for larger version. 

Name:	SelectAll.JPG 
Views:	14 
Size:	98.7 KB 
ID:	20109

    Now I have also tried with expression in AssetCategory field: IIf( Forms![Employee-Report]![cboAssetCat1] ="All", "*", Forms![Employee-Report]![cboAssetCat1])
    which returns any category that I select from the combo box but If I select "All" it returns 0 records
    As I'm not a Programmer and creating a database with my minimal knowledge, seeking your expert advice or any other way to do it.
    Thanks
    Regards
    Leo

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,708
    See if this helps


    You have not told us much of your application. The fields in the tables you have mentioned suggest that there may be hidden entities. You may need to review your structure and normalize the tables. Perhaps I'm reading the fields incorrectly.
    Please tell us about the proposed database in more detail.

    I think you may need a junction table to show which asset is assigned to which employee....

  3. #3
    leonardbd is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Mar 2015
    Posts
    3

    The Combobox "All" is showing in the list but doesn't filter All data.

    Quote Originally Posted by orange View Post
    See if this helps


    You have not told us much of your application. The fields in the tables you have mentioned suggest that there may be hidden entities. You may need to review your structure and normalize the tables. Perhaps I'm reading the fields incorrectly.
    Please tell us about the proposed database in more detail.

    I think you may need a junction table to show which asset is assigned to which employee....

    Hi,

    Thanks for the reply. The Link you have provided helped me with listing the "All" text in my combobox but it didn't filter or show All data when I'm selecting "All" from the Combobox.

    Let me explain the database in easy way.
    I have a Query table called "EmployeeAsset Query"
    I have a Asset Category table "AssetCategory" that can be referred in my Combobox for selecting Asset Category
    I Have a Form where I want to put the Combobox named "cboAssetCategory" and a Query button to call the Query Table "EmployeeAsset Query"
    Now I wnat to query to filter the data according to the AssetCategory selected in my combobox. I Have managed to filter the query adding a filter Criteria "[Forms]![Employee-Report]![Combo_Category]" in my Query Table "EmployeeAsset Query" field "EmployeeAssetCategory" which filters data according to my combobox selection.
    .
    But I also want to add "All" selection to filter all category data.
    Now I have managed to add the selection "All" in my combo list with the help of your provided link but I cannot make it work with my filter criteria. When I'm selecting "All" it doesn't return any record from the Query table.
    From your given link Following is the way that I have used to put "All" option in my combobox.
    ===========================
    To add All in the droplist of an unbound combobox, replace the RowSource property of the combobox with the following SQL string, replacing "yourComboColumn with the actual column name in your application.:


    1. SELECT YourComboColumn As Filter, YourComboColumn FROM YourTable
    2. UNION SELECT "*" As Filter ," All" As YourComboColumn FROM YourTable
    3. ORDER BY YourComboColumn;


    The union query will generate 2 columns, the first is to be used as the bound (hidden) column, and the second will be used as the displayed column.so be sure and set these properties for your combobox:
    Columns = 2
    Bound Column = 1
    Column Widths - 0; 2

    Note the space before the letter A in " All".....this is designed so that All will sort as the top choice.in the collation sequence.

    ================================================== =====
    PLease help me to complete the filter. I hope the above will describe my database easy.
    Regards\
    Leo

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 12-29-2013, 11:21 AM
  2. Replies: 1
    Last Post: 05-09-2013, 07:54 PM
  3. Replies: 2
    Last Post: 11-20-2012, 03:21 AM
  4. Expression Builder in Access Reports
    By csland in forum Access
    Replies: 1
    Last Post: 08-22-2011, 08:00 AM
  5. Replies: 1
    Last Post: 11-23-2010, 01:30 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums