Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Apr 2004
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Criteria (2003)

    I have a query with a field that gets its criteria from a combo box on a form. I want to be able to leave the combo box blank on the form and have the query ignore any criteria or choose one of the options from the combo box and have the query filtered by that selection. What is the best way to be able to do that? My current entry in the query field criteria is the following. =[Forms]![ERP Reports]![cmboGroupName] but if I leave the combo blank I don't get any data.

    Thanks,

    Kent

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

    Re: Query Criteria (2003)

    Enter [Forms]![ERP Reports]![cmboGroupName] in the first empty colum in the query grid.
    Set the criteria for this new column to

    [FieldName] Or Is Null

    where FieldName is the field for which the criteria are.
    Remove the original criteria from this field.

  3. #3
    Star Lounger
    Join Date
    Apr 2004
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Criteria (2003)

    Hans

    I tried your suggestion but couldn't get it to work. My query has a field called Supplier_Group. Some records have data in this field others do not. If I leave the combo box on the form blank I would like all records to show in the query whether they have data in the field or not. If I choose a group name from my combo box I want only those records with that group name to show in the query. In trying what you suggested I either only got records that did not have data in the Supplier_Group field when I left the combo box blank, or I got zero records returned when I chose one of the group names from the combo box.

    Appreciate you helping on this.

    Kent

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

    Re: Query Criteria (2003)

    Have you removed the criteria you originally had for Supplier_Group? That's essential.
    You must enter [Forms]![ERP Reports]![cmboGroupName] in the Field row of an empty column, and set the criteria for this column to

    [Supplier_Group] Or Is Null

    An alternative is to leave the original criteria in place, but to change them to

    [Forms]![ERP Reports]![cmboGroupName] Or [Supplier_Group] Is Null

    Access will probably change the design when you save, close and reopen the query, that's to be expected.

  5. #5
    Star Lounger
    Join Date
    Apr 2004
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Criteria (2003)

    Thanks Hans! The first one actually did work after I changed the bound column to 2 instead of 1 on the combo box. The second scenario didn't work with putting [Forms]![ERP Reports]![cmboGroupName] Or [Supplier_Group] Is Null in place of the original criteria. It would bring up records with no data in the supplier_group field along with records with the specified group name from the combo box. If I left the combo box blank then it would only bring records that had no data in the supplier_group field. The first suggestion works perfect though!! Thanks again.

    Kent

Posting Permissions

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