Query returns no rows when <> 0 applied

[Guest]

Here's my query:

SELECT TBL_RAW_IMPORT.EXCLUDE_FLAG
FROM TBL_RAW_IMPORT
WHERE (((TBL_RAW_IMPORT.EXCLUDE_FLAG)<>0));

EXCLUDE_FLAG is an Integer field

The thing is, I get no records returned at all, but this returns all the
records with 0s in it

SELECT TBL_RAW_IMPORT.EXCLUDE_FLAG
FROM TBL_RAW_IMPORT
WHERE (((TBL_RAW_IMPORT.EXCLUDE_FLAG)=0));

Another abnormal behaviour is using Not Like '*EXCLU*' for example on
another field. Not Like returns nothing, but using just Like returns the
appropriate records.

I am using Access 2003, and I have never encountered this behaviour before.

Has someone experienced this before? Can someone lead me to a possible
solution? Is it a setting in access I have to change? I am totally lost!!!!

 

[Guest]

That is strange. Is this table in Access or something like SQL Server?

Will the following work?
Not 0
<0 Or >0

 

[Guest]

Hi Jerry. Thanks for the reply. Not at all, this table is in Access. It's an
..mdb file, so nothing special about the database. I tried it on another
machine, with Access 2003 installed, but got the same result. I tried
importing the table in a fresh database, but I am getting the same result!

 

[Guest]

Actually, this is not working only on the 'Not', '<>', 'Not Like' statements
- so statements that exclude specific or a range of data are not working, the
query comes back empty. I also tried to build it through code, but I am
getting the same results.

 

[Guest]

Is the field possibly Null?

 

[Guest]

Now there's one more thing to it:

When I try Is Not Null or Is Null it returns the correct number of records

So Is Null returns the correct records for me. It does not give me anything
that has data in it. However I am not understanding this behaviour?

So
WHERE (((TBL_RAW_IMPORT.EXCLUDE_FLAG) Is Null ));

works, but not for my overall purpose, as I will have some null records and
different flags for different records. So I need to specify a criteria more
detailed that Is Null or Is Not Null.

What am I missing here?

 

[Guest]

Duane, you are a star. Your question provided the solution.

If I Updated the field with a "" (double quote) it got rid of the Null flag
and I get the records. So it had to do with the Null value.

I do not think I have ever encountered this before in the long list of
Access solutions I have built. I do understand the behaviour now, as I was
asking the query for anything that's different from 0, but there was nothing
available as all other rows were Null, so it did the right thing, but is this
the case with an Access 97 or 2000 database as well? I am little confused!

 

[Guest]

It has always been my experience that attempting to filter on a possibly null
field value requires converting Nulls to something else. You could use
something like:

SELECT TBL_RAW_IMPORT.EXCLUDE_FLAG
FROM TBL_RAW_IMPORT
WHERE Nz(EXCLUDE_FLAG,1)<>0;

 

[Guest]

Sure. Thanks again!


--
Leo

It has always been my experience that attempting to filter on a possibly null
field value requires converting Nulls to something else. You could use
something like:

SELECT TBL_RAW_IMPORT.EXCLUDE_FLAG
FROM TBL_RAW_IMPORT
WHERE Nz(EXCLUDE_FLAG,1)<>0;

 

[Rick Brandt]

It has always been my experience that attempting to filter on a possibly null
field value requires converting Nulls to something else. You could use
something like:

SELECT TBL_RAW_IMPORT.EXCLUDE_FLAG
FROM TBL_RAW_IMPORT
WHERE Nz(EXCLUDE_FLAG,1)<>0;

More efficient would be...

SELECT TBL_RAW_IMPORT.EXCLUDE_FLAG
FROM TBL_RAW_IMPORT
WHERE EXCLUDE_FLAG <> 0
OR EXCLUDE_FLAG Is Null

 

[Rick Brandt]

Now there's one more thing to it:

When I try Is Not Null or Is Null it returns the correct number of records

So Is Null returns the correct records for me. It does not give me anything
that has data in it. However I am not understanding this behaviour?

So
WHERE (((TBL_RAW_IMPORT.EXCLUDE_FLAG) Is Null ));

works, but not for my overall purpose, as I will have some null records and
different flags for different records. So I need to specify a criteria more
detailed that Is Null or Is Not Null.

What am I missing here?

You ALWAYS have to include " OR Is Null " when doing negative tests (Not In(),
Not Like, or <>) on a field that might contain a Null. That is universally
true in all databases.