Adjusting Search Query to deal with empty Fields

[northernfrog]

First I want to thank the db experts for the tremenous amount of helpful information I've found reading this forum. I'm building a document library db. One
of its' purposes is to record details about documents such as date created, date revise, owner etc... For existing documents much of this information is
either undetailed or unknown and will be determined and entered when the db is complete.

A search form uses the format of the Searching and Sorting example provided by Oldsoftboss (a godsend - many thanks for that!) without the sort capability.
The search form list is populated by a query of DocId(pk), DocTitle, and OwnerId(fk) in the main table. Population of the list and searching works perfectly.
Using the OwnerId, however, isn't an appropriate item for an end user to search by so I'd like to use the OwnerDesc field from tblOwner instead.
The problem comes when I try to include that field. At this point many documents don't have owners and it can be anticipated that even when missing detail
is entered into to the db there will be a document or two that will not be immediately assigned an owner. While the original query shows all documents whether or not the OwnerId field is blank, as soon as the OwnerDesac field is added the only documents listed are those with an OwnerId.

I've searched and read the forums for hours during the past 3 days trying to figure out how to adjust the query. None of adding Is Null to the seach criteria to OwnerId or OwnerDesc or both; innner and outer joins; adding expressions to the exisitng search fields or to blank ones has worked. A whole lot of trial and error without success. Checked the QBF examples, but they didn't seem to be the answer. I'm getting more confused as to where the answer may lie.

I have other questions about the db which I will ask later in the appropriate area; but for now any suggestions and help on this would be greatly appreciated.

 

[mearle]

No reason why this shouldn't work.
You should have a LEFT JOIN to the Owner table.
What about the WHERE clause - how are you implementing the IS NULL test?
In SQL, you should have something like:
WHERE (OwnerDesc LIKE 'xxxxx' OR OwnerID IS NULL) AND .......
- note the brackets.
In query design, this would be shown as 2 rows in the WHERE criteria - due to the OR for different fields.

The following should also work:
WHERE (OwnerDesc LIKE 'xxxxx' OR OwnerDesc IS NULL) AND .......
assuming there are no null OwnerDesc entries in the table.
This could be expressed in one WHERE criteria row in Query design.