Solved

How can something be equal to nothing??

Posted on 2013-05-22
3
444 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

685 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