Join with null values

[Tskutnik]

Hey all,

My query has multi-field joins between 2 tables. Is it possible to include Null values on both sides of the joined tables as equal values in one of the joins, so the records relate?

In this case the first join is on actual values (the normal way), but the second could have null on both sides, which I'd want to relate as equal.

Thanks

 

[boerbende]

I think you'll have to look into "left join" or "right join" queries

 

[isladogs]

Hey all,

My query has multi-field joins between 2 tables. Is it possible to include Null values on both sides of the joined tables as equal values in one of the joins, so the records relate?

In this case the first join is on actual values (the normal way), but the second could have null on both sides, which I'd want to relate as equal.

Thanks

As already described by Ben, use outer joins.
However you need to be aware that Null doesn't equal anything - not even another null

Try typing this in the VBE immediate window
?1=1
The result is true (obviously)

?2=1 gives False

?Null=Null
The result is Null

 

[Pat Hartman]

This is indicative of a design flaw. The multi-fields should probably be in separate tables since they are optional.

However, if you are not prepared to redesign, you might be able to solve the problem by changing the datatype of the optional fields to text. You also have to make the default ZLS rather than null and when you append data, convert any null values to ZLS.

As has already been mentioned, Null is nothing and Null = Null returns null. Since, ZLS is something that means ZLS will = ZLS allowing you to join the two fields.

 

[Mark_]

@Pat,

I take it ZLS = "Zero Length String", or ""?

 

[Pat Hartman]

Yes. ZLS = Zero Length String. It is coded as --- """ ---- two double quotes with nothing between them and is different from Null.

 

[isladogs]

Yes. ZLS = Zero Length String. It is coded as --- """ ---- two double quotes with nothing between them and is different from Null.

Methinks you were a little too keen typing double quotes as I can see three!

 

[Pat Hartman]

Thanks Colin. Apparently I have developed a stutter.

 

[Mark_]

Just trying to cut down on the unexplained MLAs floating around here. I wrote a paper on proper use of MLAs a while back.

B-) Just so anyone who's really interested knows, MLA = Multi Letter Acronym.

 

[isladogs]

LOL. You were the person who explained what OIC stood for.

Great site explaining MLAs here https://www.acronymfinder.com

 

[Mark_]

@Colin,

Rather than risk having anyone bother the Officer in Charge, I thought it prudent to explain his meaning...

 

[isladogs]

At the time, I wondered if he was calling me an oick....!

 

[CJ_London]

From Colins link I see ZLS also stands for Zero Lifetime Space - now does anyone want to explain what that is?

 

[isladogs]

It means spending so long on forums answering questions that you don't have time to develop your own applications....

 

[Mark_]

Or have one of these so called "Lives"?