It makes sense that Access (JET) does not match on the Nulls. If you think
of Null as meaning Unknown, the matching two things because they are both
Unknown would be wrong.
If your data needs to distinguish between unknown (Null) and
known-to-not-exist (usually represented by a zero-length string), you could
set the Allow Zero Length property of the primary and foreign key fields.
Without testing it, you should find that the ZLSs match, and you can still
have the Nulls that don't. In the real world, this solution would be useful
only in rare cases.
If you have data that needs to be assumed to match on non-null values, you
could use the Nz() function in the FROM clause of the query statement. Open
your query in SQL View, and change the FROM clause to something like this:
FROM Table1 INNER JOIN Table2 ON
CLng(Nz(Table1. ID, 0)) = CLng(Nz(Table2. ID, 0))
The query can no longer be used in Design View, and it will be quite
inefficient to execute, but it should do what you ask.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
"Larry Peeters" <lp******@prima plan.com> wrote in message
news:70******** *************** ***@posting.goo gle.com...
Hi,
I have created a very simple query to link two tables on 4 fields.
However, in certain cases, one of the fields used to link the table
may contain nulls (in both tables, so this should still be a match).
Access won't return these records. If the fields are filled with
actual values, everything is fine.
Can somebody help me on this one?
regards,
Larry Peeters:
