Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jul 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    57
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Using a form to input query criteria

    Access 2007

    I'm using a form to collect the criteria data for queries and it works when I input a single specific criteria. However, sometimes I need to query on all of the data in a field or just some of the data. When I leave any of the input fields blank, the query returns nothing. Some of the fields are numeric and some are text.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I just want to make sure I am clear about this.

    Your form has a number of controls on it. If you fill them all in, the query which uses them all as criteria works.

    But you want to be able to fill in just some of the controls, and then you want only the ones with data in them to be used as criteria.

    If that is right, then the only solution I know is one that involves a lot of VBA code.

    When you have a query with criteria, the criteria make up the "Where" clause of the SQL of the query.

    In the situation you describe you need to build the SQL in code, then build the Where clause looking at each of the controls in turn and using the ones that are not null.

    We can go further and look at what that VBA code would look like if you want.
    Regards
    John



  3. #3
    Star Lounger
    Join Date
    Jul 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    57
    Thanks
    4
    Thanked 0 Times in 0 Posts
    I didn't explain this well! The query works OK if fields on the form are left blank. My problem is when I want multiple criteria in a field.

    Example: The form has a field for the 'City'. When I enter one city name in the field it works fine. Sometimes I need to query on more than one city or all of the cities. How do I specify more than one city or all cities in the field on the form?

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    To be able to Search for one city OR another you need two text boxes.

    The SQL for the query looks like this:
    Code:
    SELECT tblPeople.*
    FROM tblPeople
    WHERE (((tblPeople.Suburb)=[Forms]![frmTestSearch]![txtsuburb]));
    For an OR query it needs to look like this
    Code:
    SELECT tblPeople.*
    FROM tblPeople
    WHERE (((tblPeople.Suburb)=[Forms]![frmTestSearch]![txtsuburb1]) or ((tblPeople.Suburb)=[Forms]![frmTestSearch]![txtsuburb2])) ;
    One option is a multiselect list box, listing the towns you have. But this requires VBA code. It can't be done with a simple query.

    You can tell the query to treat an empty text box to mean All Cities.

    Code:
    SELECT tblPeople.*
    FROM tblPeople
    WHERE (((tblPeople.Suburb)=[Forms]![frmTestSearch]![txtsuburb])) or   OR ((([Forms]![frmTestSearch]![txtsuburb]) Is Null));
    That looks like this in the query grid.
    Citiesquery.gif

    Really flexible searching generally requires using VBA to write a Where clause
    Regards
    John



  5. #5
    Lounger
    Join Date
    Feb 2011
    Posts
    43
    Thanks
    0
    Thanked 6 Times in 6 Posts
    I have worked on some Access databases where the original developer used some pretty complex queries generated by the query builder.

    So while VBA is not the only way, IMO VBA is the must way to go for simplification of debugging.

    Cronk

Posting Permissions

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