Query:Null Values Screened Out

[Larry R Harrison Jr]

The database is located at this link:

http://www.angelfire.com/az/larrytucaz/tmp/db1.zip

This database has a query which sometimes screens to leave out "OCB" in the
last field in the query. It is joining the "documents" table with a
"revisions" table; the revisions is linked to documents by an id field.

I have noticed that if I don't give any criteria for the custom field, it
shows all values from "documents" table and the last revision number, even
if there isn't a related record(s) in the "revisions" table. However, if I
give the <>"OCB" (or any other) criteria in that last field, it not only
filters out those with OCB in the field but it ALSO filters out those which
don't have a related record in the "revisions" table.

Why is this? And how can one force it to show show, say, "N/A" as the value
in this field if there are no related records in the "revisions" table?

LRH

I noticed that when it does this, it also filters out those which have a
null value

[Peter Doering]

On Fri, 7 Nov 2003 22:59:00 -0700, Larry R Harrison Jr wrote:

...
I have noticed that if I don't give any criteria for the custom field, it
shows all values from "documents" table and the last revision number, even
if there isn't a related record(s) in the "revisions" table. However, if I
give the <>"OCB" (or any other) criteria in that last field, it not only
filters out those with OCB in the field but it ALSO filters out those which
don't have a related record in the "revisions" table. Why is this?
No idea what you mean. There are no OCB's in your DB. If you want to filter
you can include it in the sub query's WHERE clause.
And how can one force it to show show, say, "N/A" as the value
in this field if there are no related records in the "revisions" table?
LatestRevisionNumber: Nz((SELECT Max(ToRevision) FROM [Table of Revisions]
WHERE [Table of Revisions].DocID =[Table of Documents].DocID),"N/A")
I noticed that when it does this, it also filters out those which have a
null value

Correct. This won't happen with above line anymore.

HTH - Peter

--
No mails please.

[Allen Browne]

Larry, that's how Null works. See:
The Query Lost My Records!
at:
http://members.iinet.net.au/~allenbrowne/casu-02.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Larry R Harrison Jr" <la*******@cox.net> wrote in message
news:FC%qb.4368$0K6.2351@fed1read06...

The database is located at this link:

http://www.angelfire.com/az/larrytucaz/tmp/db1.zip

This database has a query which sometimes screens to leave out "OCB" in the last field in the query. It is joining the "documents" table with a
"revisions" table; the revisions is linked to documents by an id field.

I have noticed that if I don't give any criteria for the custom field, it
shows all values from "documents" table and the last revision number, even
if there isn't a related record(s) in the "revisions" table. However, if I
give the <>"OCB" (or any other) criteria in that last field, it not only
filters out those with OCB in the field but it ALSO filters out those which don't have a related record in the "revisions" table.

Why is this? And how can one force it to show show, say, "N/A" as the value in this field if there are no related records in the "revisions" table?

LRH

I noticed that when it does this, it also filters out those which have a
null value