Solved

How can something be equal to nothing??

Posted on 2013-05-22
3
398 Views
Last Modified: 2013-05-22
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
Comment
Question by:11ptMan
3 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 39188756
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
 

Author Closing Comment

by:11ptMan
ID: 39188815
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39189540
>>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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now