left join with Nz works differently in report

  • Thread starter Thread starter BJS
  • Start date Start date
B

BJS

Let's say I have an EmpDoc table with the following
records:
Fred, Document1
Fred, Document2

The DocRead table (Employee, Document, DateRead,
Applicable) has:
Fred, Document1, 9/1/2004, Yes

If I do a left join and try to get a record that has
IsAppl=Yes for Docs that have not yet been read:
SELECT EmpDoc.Employee, EmpDoc.Document, Nz([Applicable],-
Yes) AS IsAppl
FROM EmpDoc LEFT JOIN DocRead ON (EmpDoc.Document =
DocRead.Document) AND (EmpDoc.Employee = DocRead.Employee);
the query works fine, returns -1 for both records. But if
I then base a report on this query, the Document2 record
shows IsAppl as 0, not -1. Using IIf instead of Nz has
the same result. Any ideas before I pull all my hair out?
 
Hi,


And what is -Yes supposed to be? If you use - as NOT, that fails:

? CBool(yes) = CBool( - yes)
True


which shows that yes and -yes are, as Boolean , the same thing. Why not
using 0 rather than ( - yes )?


Hoping it may help,
Vanderghast, Access MVP
 
Thanks for your response. Unfortunately, that -Yes
should have been Yes in my original post, that's a typo,
not the issue. The issue is that the recordset in the
query window differs from the report results.

BJS
 
Back
Top