Re: outer Join and NULL values...?!?!!!

V. & M.,

Now you two have my wheels turning...

Is it too late to put a wishlist item in for Access 2007?

Wishlist Item:
On the Join Properties dialog have MS. Add a "Match Null values = Null
Values on this join" option checkbox and have the Query designer emit your
Nz(a.f3, True) = Nz(b.f3, True)
syntax in the resultant query SQL if selected.

....better yet, add a "complex Join Expression constructor" [...] button to
open a second dialog box so we can build "LIKE" or non-equiJoin expressions
through the designer for each/all joins between the two tables affected by
the selected join...

<*sigh*> ...what could've been...</*sigh*>

Does anybody know if we lose this mahhvelous ability in the new Access '07
ACE engine (I'm DL-ing the just released Beta 2 for a look...)?


"Michel Walsh" wrote:

Hi,


If you want null matching another null, you can coalesce, Nz(), them to a
value that is known to not present in your data, such as -1 for a field
having only positive values:

FROM .... SOME JOIN ....
ON
a.f1=b.f2 AND
a.f4=b.f4 AND
Nz(a.f3, -1) = Nz(b.f3, -1)


If you want a null matching any thing:

FROM .... SOME JOIN ....
ON
a.f1=b.f2 AND
a.f4=b.f4 AND
Nz(a.f3 = b.f3, true)


and if you want a null matching nothing, well, just leave it as it is.

FROM .... SOME JOIN ....
ON
a.f1=b.f2 AND
a.f4=b.f4 AND
a.f3 = b.f3



Null are as useful as 0 is, but you probably just re-experience what the
introduction of the 0 has been 400 years ago (in Europe, that is). And since
no one is born with that knowledge, many of us, like you, just got "that
moment". And since it would hardly kill you, I hope it won't, it will just
make you stronger! :-)




Hoping it may help,
Vanderghast, Access MVP.

.