Administering null returned values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi to all!
Maybe I've found a bug into Access 2000...

Can I configure the dbms to delete the null values from a result query
without using the WHERE clause? Or is it a characteristic of Access?

I made this query:

SELECT Country
FROM Tab

and the result was:

empty row
country1
contry2
....

I looked at the database and saw that a column named Country has 4 empty rows.

Then I found one interesting thing. I made another query:

SELECT Country
FROM Tab
WHERE Tab.anyColumn NOT IN ('someValue')

The result in this case was:

country1
country2
....


Please help me, i wanna know how works it.
 
A "Null" is an undefined non-value ... it means the absence of anything you
could construe as a value. You can only "replace" a Null with a
defined/knowable value -- you can't "delete" a null because it isn't a
value.

Are you saying you want to run a query that does not return rows if the
underlying table has a Null in a field? If so, that's the definition of a
WHERE clause.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Its not a bug, or indeed a characteristic of Access per se, its due to the
nature of Null. The key thing to appreciate about Null is that its not a
value, but the absence of a value, in effect its an 'unknown'. Because of
this any comparative operation involving Null will evaluate to Null, not to
True or False; this makes sense as comparing an unknown with anything must
result in an unknown (the unknown night equal the value its compared with or
it might not), i.e. Null. This is why your query with the WHERE clause
doesn't return the Null rows, because NULL NOT IN('somevalue') evaluates to
NULL rather than TRUE.

Arising for this you cannot test for something = NULL, or something <> NULL
because the result would always be NULL. To find if a column position in a
row contains Null you test for IS NULL or IS NOT NULL in a query, so if you
wanted to delete the rows in your table where the Country column is Null
you'd use:

DELETE *
FROM Tab
WHERE Country IS NULL;

To remove the Null rows from a SELECT query's result set you'd use WHERE
Country IS NOT NULL, but you can prevent Nulls ever getting into a column in
the first place by setting the column's Required property to True in table
design view. This is the equivalent of the NOT NULL constraint in SQL's Data
Definition Language (DDL) which is used for creating or amending table
definitions etc.

Ken Sheridan
Stafford, England
 
Thank you, now is more clear.

One more thing: does DISTINCT delete null duplicates? Cause in fact I've
used the DISCTINCT clause in my query, and how I sad, there are 4 empty rows
in the db, but the result set was with only one empty row.

with regards,

bimbica
 
"Empty" means "I can't see anything". That could be because of a Null, or
could be because of a zero-length string.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
If the query returns only the one column then all the rows with NULL at that
column position will be returned. This makes sense as one NULL is not 'the
same as' another NULL because of the fundamental nature of NULL as an
'unknown'; one 'unknown' does not necessarily equal another 'unknown'. The
rows are therefore not DISTINCT.

However, if you return two columns in the query and there are multiple rows
with NULL at one column position and the same 'real' value at the other
column position in each of those rows, then only one instance of those rows
will be returned. This is usually what you'd want, but is not really
consistent with the true nature of NULL as the rows are still not truly
DISTINCT; its practicality triumphing over theory!

I'm away for a couple of weeks from tomorrow, so this will be my last log-in
until then. Good luck.

Ken Sheridan
Stafford, England
 
I did undestrand the nature of null now. You were very helpfull. So thanks
and
Have a nice holiday!
 
Back
Top