Pulling Up Null values

[Bill Davis]

I have a column that has no data in some of the fields
but when I use "Is not Null" in the Criteria it still
bring up all the records and when I use "Is Null" it
brings up no records . Why is this happening and how can
I only pull up the values that have information or blanks.
Thanks in advance

 

[Brian Camire]

Is the field you're placing the criteria on a text field? If so, is the
value of its Allow Zero Length property set to Yes?

A text field with the Allow Zero Length property set to Yes can contain
zero-length strings, which are different from Null. So, if all the "blank"
fields actually contain zero-length strings, this would explain the results
you are seeing.

You can test if this for this using a query whose SQL looks something like
this:

SELECT
[Your Table].*
FROM
[Your Table]
WHERE
[Your Table].[Your Field] = ""

This will return all records from "Your Table" where the the value of "Your
Field" is a zero-length string.

 

[Van T. Dinh]

You may zero-length String "" (which you can't see, the same with Null)
rather than Null and ZLS is *different* from Null.

 

[Guest]

That is what was going on.. I am able to up like ""and
not like to get the results I am looking for.
Thank you very much.
Bill

-----Original Message-----
Is the field you're placing the criteria on a text field? If so, is the
value of its Allow Zero Length property set to Yes?

A text field with the Allow Zero Length property set to Yes can contain
zero-length strings, which are different from Null. So, if all the "blank"
fields actually contain zero-length strings, this would explain the results
you are seeing.

You can test if this for this using a query whose SQL looks something like
this:

SELECT
[Your Table].*
FROM
[Your Table]
WHERE
[Your Table].[Your Field] = ""

This will return all records from "Your Table" where the the value of "Your
Field" is a zero-length string.

I have a column that has no data in some of the fields
but when I use "Is not Null" in the Criteria it still
bring up all the records and when I use "Is Null" it
brings up no records . Why is this happening and how can
I only pull up the values that have information or blanks.
Thanks in advance

.