Solved

How can something be equal to nothing??

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

948 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

22 Experts available now in Live!

Get 1:1 Help Now