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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

V.,

awww...come on... is it an announcement if you just give us a hint? ;-)

As for your second point, I think history is on my side, so I stand by the
appropriateness of asking the question.
Let me remind you that you are saying this about the folks who turned Visual
Basic 6 into VB.Net 1.0! (specifically, see features like: "edit & continue"
which didn't return until _2 versions later_!)...'nuff said?

"Michel Walsh" wrote:

Hi,


I think it is preferable to let Microsoft make the announcements first :-),
but it would be hard to imagine loosing functionalities... people would just
not upgrade if it was so!


Vanderghast, Access MVP


"Mark Burns" <MarkBurns@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1F99800E-54CE-44D7-95B9-8DEF4AFD1DD3@xxxxxxxxxxxxxxxx
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.




.

Relevant Pages

  • Re: outer Join and NULL values...?!?!!!
    ... "Michel Walsh" wrote: ... not upgrade if it was so! ... Values on this join" option checkbox and have the Query designer emit ... If you want null matching another null, you can coalesce, Nz, them ...
    (microsoft.public.access.queries)
  • Re: outer Join and NULL values...?!?!!!
    ... but it would be hard to imagine loosing functionalities... ... not upgrade if it was so! ... Values on this join" option checkbox and have the Query designer emit your ... If you want a null matching any thing: ...
    (microsoft.public.access.queries)
  • Re: outer Join and NULL values...?!?!!!
    ... Now you two have my wheels turning... ... Values on this join" option checkbox and have the Query designer emit your ... open a second dialog box so we can build "LIKE" or non-equiJoin expressions ... If you want a null matching any thing: ...
    (microsoft.public.access.queries)
  • Re: Callling a function from another file...
    ... is the single most significant improvement in the history of the C ... language. ... Every time my compiler catches an error due to the ... function call not matching the prototype, ...
    (comp.lang.c)
  • Re: ot: amazons recommendation engine
    ... you're almost certainly right for sales - but why recommend something ... for rental that doesn't even come close to matching my profile ...
    (uk.rec.motorcycles)