By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,512 Members | 3,689 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,512 IT Pros & Developers. It's quick & easy.

Query:Null Values Screened Out

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.
Nov 12 '05 #2

P: n/a
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

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.