Nulls

[ryetee]

I'm quite confused about Nulls.
Or maybe I'm not. Maybe I'm confused about something else but have chosen to believe it has something to do with Nulls. It's driving me mad!!
Anyway here goes.

I have a table.
It has a field called notes. A lot of rows have "historical data" in this field.
The rest have something. They are either blanks, spaces, unprintable characters or NULLS!!!!!

I have 2 queries based on this table.
They are identical except query 1 has as it's criteria against the notes field = "historical data". The 2nd query has <> "historical data".

Query 1 returns all the rows with "historical data" in the notes field.
Query 2 returns nothing.

Why?
I'm sure I've read somewhere I can get around this using NZ - how I'm not sure at the moment but I'll either work it out or someone will tell me.

But does this mean that every field effectively has to be tested for NULLS?

Also is there a simple way to see what fields have got NULL in (can I see the data in EBCIDIC HEX or whatever else is out there these days?).

 

[BigHappyDaddy]

Interesting. Try something like this in the 2nd query:

 WHERE NZ(tblName.Notes,"") <> "historical data"

 

[plog]

But does this mean that every field effectively has to be tested for NULLS?

Yes. This all stems from math. Rememeber when you went round and round that day about the empty set and 0?

Teacher: The empty set has no value, its not even applicable. 0 is a value so its not part of the empty set.
You: You're saying 0 is nothing?
Teacher: Right, it's value is effectively nothing
You: And the empty set is nothing?
Teacher: Right, it has no value at all
You: So the empty set is nothing and 0 is nothing, but they aren't the same nothings?
Teacher: Exactly.

In a database the same logic applies. If you want to exclude a specific value (<>"historical data") but include null values, you must explicitly say so. Think of it like this--your query has to first determine the fields value if that value is the string "some value" it then compares that value to "historical data" and sees they are different and lets it through. When it comes to a Null it can't do a comparison because there is no data there, so it kicks it out because it can't even do a comparison to "historical data".

So, because of that negation (<>"historical data") you must test for nulls if you want to include them. If you were looking for a specific value, it would work, but like I said with a negation it can't even retrieve the value of a Null to start the comparison so it immediately fails.

 

[The_Doc_Man]

The issue could very well be nulls. The problem is that NULL isn't equal to anything and - as you have found out - it also isn't NOT EQUAL to anything. It is the ultimate speed bump.

The reason that your query works when looking for "historical data" in that field is that the syntax for " [Notes] = 'historical data' " works correctly when [Notes] is NULL because (as stated above) NULL is not equal to "historical data" so would not have been selected anyway. Only 'historical data' is equal to 'historical data' and so the selection works fine. But the one that trips you is " [Notes] <> 'historical data' " and there, the speed bump rears its ugly head. Access just doesn't like NULLs in comparisons, which is why the NZ function exists. BigHappyDaddy gave you a good example so I won't repeat it.

I have a different question for you. Is that field ever anything OTHER than "historical data"? Because if it is never anything other than "historical data" or a blank, why not make it a check-box that defaults to FALSE. Then you would have no NULL issues ever. Of course, if the field has other values, that doesn't apply, but it was just a thought.

 

[ryetee]

Interesting. Try something like this in the 2nd query:

 WHERE NZ(tblName.Notes,"") <> "historical data"

Did more or less that to get around it but still don't understand NULL, bank etc. Maybe the posts below (or above as soon as I post this) will shed light!!

 

[BigHappyDaddy]

So blank, as you call it is what I normally call an empty string "". It is a string of no characters, but it is a string. A number field/variable that contains 0 contains something. It contains the value "0". A field/variable that is NULL contains nothing. An empty void. An undefined value. A black hole. I (and Access) can compare 0 to any other value. I can compare an empty string to any other string value. But I cannot compare NULL to anything because NULL is nothing.

Clear as mud?

 

[ryetee]

The issue could very well be nulls. The problem is that NULL isn't equal to anything and - as you have found out - it also isn't NOT EQUAL to anything. It is the ultimate speed bump.

The reason that your query works when looking for "historical data" in that field is that the syntax for " [Notes] = 'historical data' " works correctly when [Notes] is NULL because (as stated above) NULL is not equal to "historical data" so would not have been selected anyway. Only 'historical data' is equal to 'historical data' and so the selection works fine. But the one that trips you is " [Notes] <> 'historical data' " and there, the speed bump rears its ugly head. Access just doesn't like NULLs in comparisons, which is why the NZ function exists. BigHappyDaddy gave you a good example so I won't repeat it.

I have a different question for you. Is that field ever anything OTHER than "historical data"? Because if it is never anything other than "historical data" or a blank, why not make it a check-box that defaults to FALSE. Then you would have no NULL issues ever. Of course, if the field has other values, that doesn't apply, but it was just a thought.

Thanks to you and plog I'm now "getting it".
Reason I have this problem is I've redesigned a database as the old one had been butchered together and wasn't entirely relational! I now have to take the old data and get it into my new database and when I'm analysing the data I'm hitting these speed bumps!!

Is there a reason why there should be NULL values for anything? Did this come from the day when storage was at a premium? Can I easily change every null value in any field in any table into some value that doesn't give me grief!!?

 

[BigHappyDaddy]

Can I easily change every null value in any field in any table into some value that doesn't give me grief!!?

Sure! I would use an update query to set a "default value" to a field that contained NULL.

WHERE NZ(tblName.Notes,"") = ""

This WHERE statement will return all the records where Notes truly contains an empty string AND all the records where Notes is NULL.

Then I would then:

SET tblName.Notes = ""

It updates the Notes field to an empty string. Obviously no change to all the records that are already empty string, but now all the records with NULL are also empty string.

All the records that already have a value (other than empty string) are excluded by the WHERE statement.

 

[ryetee]

So blank, as you call it is what I normally call an empty string "". It is a string of no characters, but it is a string. A number field/variable that contains 0 contains something. It contains the value "0". A field/variable that is NULL contains nothing. An empty void. An undefined value. A black hole. I (and Access) can compare 0 to any other value. I can compare an empty string to any other string value. But I cannot compare NULL to anything because NULL is nothing.

Clear as mud?

Yes!
And as my message isn't long enough to post I will now pad it out with NULLS!

 

[vbaInet]

I will add a few more points:

1. In response to your last post, because Null isn't a value you're actually going to set the field to Null. Notice I didn't say you're going to set the value of the field to Null. It's almost like resetting the field to a clean slate. Well everyone has already explained this point anyway.
2. If you don't want to deal with differentiating between Null and the empty string "", set the Allow Zero Length property of the Text field to No.
3. When querying data it's more efficient filtering out Nulls than filtering out the empty string "". Even aggregate functions like Count, Sum etc are optimised to ignore Nulls.