combos as search criteria: problem with empty fields 1

[Davide77]

Hallo,

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.

Do you have any suggestions

 

[SarahG]

HI,
try setting the properties of the combo boxes as follows:
Default value= "*"
Limit To List=Yes

This way, the comobs will start off being filled, and can't be blanked because the entry has to be in the list.
Also, if only one criteria is specified, the other two are essetnially ignored because it's selecting all values for these fields.
Good Luck.

 

[Davide77]

Hallo Sarah

the properties are already set like you suggested. When I wrote "as soon as there are empty fields it doesn't work properly" I wanted to say that there are empty fields in the table/query I'm searching. For example I have a record with the person name, but I don't know the type and country yet. If i will search this table query it won't show me the record that has empty fields.

 

[SarahG]

OK, I've looked at it more closely now.
You'll need to change the syntax of the WHERE clause in your SQL:

WHERE (ISNULL(tbl_Type.ID) OR tbl_Type.ID Like [Forms]![Form1]![cbo_type])
AND (ISNULL(tbl_Country.ID) OR tbl_Country.ID Like [Forms]![Form1]![cbo_country])
AND (ISNULL(tbl_Person.ID) OR tbl_Person.ID Like [Forms]![Form1]![cbo_person]);

Each part of the WHERE clause selects null fields OR matches the selection made by the user.
Hopefully this will work.

 

[Davide77]

thank for the suggestion but still it doesn't do the job.
Actually it works fine in almost every case but there are cases where it shows me records that should not show.
Example:

Record: name ("Blabla"); type (null); country ("country")

if in the form I put.

name: <all>
type: type1
country: <all>

it will show me all th records that have type1 and the records that have a null type.

What I'm tring to do is that if I select <all> it will consider all fields (null and full) and if I select something else it will consider just this selection...

 

[SarahG]

OK, modify it as follows, note the extra brackets:

WHERE ((ISNULL(tbl_Type.ID) AND [Forms]![Form1]![cbo_type]="*") OR tbl_Type.ID Like [Forms]![Form1]![cbo_type])
AND ((ISNULL(tbl_Country.ID)AND [Forms]![Form1]![cbo_country]="*") OR tbl_Country.ID Like [Forms]![Form1]![cbo_country])
AND ((ISNULL(tbl_Person.ID) AND [Forms]![Form1]![cbo_person]) OR tbl_Person.ID Like [Forms]![Form1]![cbo_person]);

 

[Davide77]

that is the solution: thanks a lot