combos as search criteria problem

[Davide77]

Hallo,
I have a form with combos for search criteria. The source of the combo are different tables and I made a UNION with "*", "<ALL>" in order to have the possibility to select every possibility for one combo.

The problem is that the <All> consider all the fields that are filled in but it leaves out the null fields.

I tried to put as criteria in the OR line: is null. But then the query will always give me also the null ones.

Do you have a suggestion for an effective search with combo boxes, that also manages null fields.

thanks

 

[SteveR77]

David,

Take a look a the following. It appears that you may have excluded null in your Union query.

http://support.microsoft.com/?kbid=210290

HTH,

Steve

 

[Davide77]

Steve,
the code I'm using as combobox source is this:

SELECT tbl_Person.ID, tbl_Person.Nome FROM tbl_Person UNION SELECT null, "<All>" FROM tbl_Person;

as shown in the faq: faq702-4538.

The example you gave me suggest to write null instead of "*", but this won't work at all...

 

[Davide77]

sorry the code I'm using is:

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