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