Solved

How can something be equal to nothing??

Posted on 2013-05-22
3
455 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 49

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

Webinar: MongoDB® Index Types

Join Percona’s Senior Technical Services Engineer, Adamo Tonete as he presents “MongoDB Index Types, How, When and Where Should They be Used?” on Wednesday, July 12, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7).

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

623 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