Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 474
  • Last Modified:

How can something be equal to nothing??

I'm trying to understand what I might have done wrong here but I'm just not seeing it.
I have two tables one in the mdb (POINTS) and other is a linked table (POINTS1) from another mdb. Both tables have the exact same structure and should have the same data.

I was writing a query to verify that they do have the same data when I discovered that I was getting a false result.  I know that there is data missing from the subtype field in the POINTS1 table.   I would think that not null would mean the Points.subtype field had some value even if it was an empty string and so even if the POINTS1.Subtype value was null that something is not equal to nothing. But I when I execute Query 4 below I get no results yet when I execute Query 3 I get the results I expected.  In this particular case I could simply use Query3 but it makes me question the results of other queries I have which do comparisons with <>.
 

Query3 = SELECT POINTS.subtype, POINTS1.subtype
FROM POINTS INNER JOIN POINTS1 ON POINTS.recnum = POINTS1.recnum
WHERE (((POINTS.subtype) Is Not Null) AND ((POINTS1.subtype) Is Null));

Query4 = SELECT POINTS.subtype, POINTS1.subtype
FROM POINTS INNER JOIN POINTS1 ON POINTS.recnum = POINTS1.recnum
WHERE (((POINTS.subtype) Is Not Null) AND ((POINTS1.subtype)<>[points].[subtype]));

The attached file is a picture of the results of each query

Can anyone explain why Query4 returns no records?
QueryResults.png
0
11ptMan
Asked:
11ptMan
1 Solution
 
mbizupCommented:
You can't use NULL (an absence of value) directly in comparisons like that -- you need to handle nulls.  Your first method is good... I believe either of these null handlers will work for you as well:


Query4 = SELECT POINTS.subtype, POINTS1.subtype
FROM POINTS INNER JOIN POINTS1 ON POINTS.recnum = POINTS1.recnum
WHERE (((POINTS.subtype) Is Not Null) AND ((POINTS1.subtype & "")<>[points].[subtype]));

Open in new window


Query4 = SELECT POINTS.subtype, POINTS1.subtype
FROM POINTS INNER JOIN POINTS1 ON POINTS.recnum = POINTS1.recnum
WHERE (((POINTS.subtype) Is Not Null) AND ((NZ(POINTS1.subtype))<>[points].[subtype]));

Open in new window

0
 
11ptManAuthor Commented:
One would think that absence of value would translate to no value and therefore not be equal to some value but since that is not the way it is I'll just thank you for your assistance.

I made that minor change and it made all the difference :-)  
now I'm going to have to review any other queries where I may have done this. :-(
0
 
PortletPaulCommented:
>>One would think that absence of value would translate to no value
this has perplexed many before you

>> and therefore not be equal
as there is an absence of value (think of it as: a void, just nothing there, nobody at home) it cannot be directly compared to a value, so it can't be equal, and it it can't be equal it also cannot be "not equal" either because there is nothing there to compare to.

so, there is a need to do something special for null
you can fill the void e.g. ISNULL([field],0)
or
you can cater for null by using "is null" or "is not null"

some folks have their doctorates by discussing null in relational databases , so I wouldn't dwell on it too much unless very interested. e.g. http://en.wikipedia.org/wiki/Null_(SQL)

>>since that is not the way
that is a sound and pragmatic viewpoint :) , you need to specifically cater for nulls in many situations.

cheers, Paul
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now