Running a Query produces no results

[Guest]

I have prepared a query based on several tables including a master table
which has the primary key. All the tables are linked to the master primary
key. I know that some fields in some of the tables have values, other tables
are blank. When running the query, no results are shown. I presume this is
because some tables have no fields with any values. How can I overcome this?

 

[Michel Walsh]

Hi,


Not necessary.


SELECT *
FROM anytable
WHERE false


won't return any record, even if there are some records in the table you
would use.


If the WHERE condition (the criteria) evaluates to FALSE or to NULL, the
row, in the result, is not kept. As example:


SELECT *
FROM someTable
WHERE fieldID = 4 AND fieldID = 5


would return nothing since for a given row, say fieldID=4, then it is false
that 4 = 5 and the criteria evaluates to : (4=4) AND (4=5) which is
true AND false, which is false. So, again, you have no record in the
result.

If a field has no value, in fact, it does get the value "NULL" (which is not
zero, but a special value telling "there is no known/available value" ) . A
comparison with such a value is very likely to produces NULL:

4 = NULL

is asking if four is equal to a value we don't know yet... the answer is...
we don't know, yet, or Null. If the criteria evaluates to NULL, the record
is not kept. So, indeed, if there is no row for which the row evaluates the
criteria to TRUE, no row will appear in the result.



Hoping it may help,
Vanderghast, Access MVP

 

[Allen Browne]

See:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

The article explains how to use outer joins and watch the criteria on the
nulls.