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

    Unanswered: combos as search criteria: problem with empty fields


    here is the problem:
    I have three tables: tbl_person (name, id, countryID,typeID), tbl_country (countryname, id), tbl_type (typename, id).
    I made a parameter query that I would like to use to display the result of a search by person name, country name and typename.
    The criteria for the search are in an unbound form and three unbound combo boxes with this sql code as rowsource:

    SELECT tbl_Person.ID, tbl_Person.Name FROM tbl_Person UNION SELECT "*", "<All>" FROM tbl_Person;

    for the person name, and so on fo rthe other two combos.

    Then in the parameter query I do like this:

    SELECT tbl_Person.Name, tbl_Type.ID, tbl_Country.ID, tbl_Person.ID
    FROM tbl_Country RIGHT JOIN (tbl_Type RIGHT JOIN tbl_Person ON tbl_Type.ID = tbl_Person.TypeID) ON tbl_Country.ID = tbl_Person.CountryID
    WHERE (((tbl_Type.ID) Like [Forms]![Form1]![cbo_type]) AND ((tbl_Country.ID) Like [Forms]![Form1]![cbo_country]) AND ((tbl_Person.ID) Like [Forms]![Form1]![cbo_person]));

    It works fine if all the fields contain data, but as soon as there are empty fields it doesn't work properly any more.
    And the problem is that no one of the fields is required, so there can be a record with country name and type, another with person and country, etc.

    Take a look to the attached example to have a clearer idea (access 2000)
    Do you have any suggestions
    Attached Files Attached Files

  2. #2
    Join Date
    Oct 2003
    Your parameter query is not allowing for nulls.

    From online help for the Like operator:

    result = string Like pattern

    If string matches pattern, result is True; if there is no match, result is False. If either string or pattern is Null, result is Null.

Posting Permissions

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