Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Posts
    45

    Unanswered: eternal problem: the filtering with comboboxes

    Hallo, I know this is a typical question, but still I'm stucked on the multiple combo boxes that set the criteria to filter some data. The situation is this:
    I have three tables: tblPerson (per_name, per_surname, per_ID, per_couID, per_typID), tblCountry (cou_id, cou_name), tblType (Typ_Id, typ_name). They are joined: a person can live in one country and be of certain type, but none of the information is mandatory. A person can not to have a name or a surname or have no type or no country.

    The problem now is to set up a form with three combos: cbopersonname, cbocountry and cbotype, and in according to the selection the query will show what match the criterias.

    The problem comes with the empty (or null) fields, for ex. a person have not a name. I would like the query to consider the selection <ALL> (*) as all the fields, also the null or empty ones.

    I'm using this code:

    SELECT [tbl_Person].[Per_Name], [tbl_Person].[Per_LastName], [tbl_Type].[Typ_ID], [tbl_Country].[Cou_ID], [tbl_Person].[Per_ID]
    FROM tbl_Country RIGHT JOIN (tbl_Type RIGHT JOIN tbl_Person ON [tbl_Type].[Typ_ID]=[tbl_Person].[Per_Type]) ON [tbl_Country].[Cou_ID]=[tbl_Person].[Per_Country]
    WHERE

    ((ISNULL([tbl_Type].[typ_ID]) And [Forms]![Form1]![cbo_type]="*") Or [tbl_Type].[typ_ID] Like [Forms]![Form1]![cbo_type])
    And
    ((ISNULL([tbl_Country].[Cou_ID]) And [Forms]![Form1]![cbo_country]="*") Or [tbl_Country].[cou_ID] Like [Forms]![Form1]![cbo_country])
    And
    [tbl_Person].[per_name] Like [Forms]![Form1]![cbo_person];


    bu with this code if a person has no name it won't be shown even if I put an asterisc in the combo (that should mean: everything)....how can I solve this?

  2. #2
    Join Date
    Jan 2004
    Location
    Vancouver, WA
    Posts
    41
    You might want to add some code to your "Search" button that builds a query. This will allow you to not include fields in your query that are not used.

    That is, if the value of a field can be anything (NULL or non-NULL), don't bother including it in your query.

    The only fields in your query should be those that have specific filter values.

Posting Permissions

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