Problem with filter (or query) on integer field with null values

[KenG]

I have an integer field in a table. Some of the records contain null values for this field. I want to filter the records by excluding a specific integer value. The filter excludes the correct records, but it also exludes all the records with null values as well. I've tried this as a select query and get the same results.
What's going on here?

 

[rafe]

try setting the Null value to something else (temporarily) in the query using the Nz() function.

So in the Query's Data Sheet's "Field" try

MyNewFieldName: Nz([FieldToTest],0)

this will set all Null values to 0... of course you could replace 0 with another value

 

[KenG]

Thank you very much, that works.
Do you have any idea why I would have to do this? It seems a little silly to have to make up a new variable for a simple where clause to function properly.

 

[rafe]

i've had the same problem... & this is how i fixed it.

1) change the underlying table's definition so that the default upon add is set to the appropriate "zero" value.

2) make sure you can select all records (& no others) with null's already in them with a select query selecting on ...Is Null...

3) change the query to an update query with the update value you choose in step #1

to answer your question about why...

a) access was designed to do this temp field thing **very** efficiently...

b) think about how data get's added so that it can reduce your brain work later on... access does the table rearrangement thing very well but you still have to keep an eye on the db design so it doesn't bite you later

 

[THoey]

I really don't think this is an Access problem, but more of a way of thinking.

You say "I want to filter the records by excluding a specific integer value. The filter excludes the correct records, but it also exludes all the records with null values as well."

A NULL value is an unknown value. So as far as Access knows, if you have a field with values 1 - 10, you allow NULLS, and you want all records except for those with the value of 7, Access doesn't know whether a NULL value should be included or not, so MS in their wisdom, I feel decided to leave those out, too.

It is always best to handle these cases, by giving a default value or by ensuring in your code or query that you test for a NULL. It is a pain at first, but after you get used to handling it, it verifies the validity of your data.

*** DISCLAIMOR *** The above is the way that I have learned to look at it and could be off base as far as MS is concerned. But it always better to be safe than sorry...

Hope that helps...



Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

 

[skfoster]

As my experience, before do anything, use update query to update fields. If it is text, update from null to "N/A", if it is number, update to 0. Doing so, will improve the performance of query.

 

[MichaelRed]

Hmmmmmmmmmmmmm,

Sime minor disagreement form my side.


Null MEANS UNKNOWN.


This is NOT the same as nothing.
This is NOT same as "N/A".
This NOT the same as ZERO!


The behaviour of NULL is important to many aspects of your data, you should include consideration of NULL in all of your data manipulations.


MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[KenG]

Thanks for all the replys.
I agree MichaelRed regarding the meaning of NULL. For my application, the NULL means that I haven't actually measured any value for this observation. It is very likely that I will have a value of zero. So setting the defalut value to zero will introduce data that was never actually measured. I think I can do a work around by using the Nz() function in a query (Thanks rafe). That way the data can maintain it's zero values.
I guess MS had to decide how to deal with this situation and decided the 'wrong' way ;-)