Pulling Up Null values

  • Thread starter Thread starter Bill Davis
  • Start date Start date
B

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
 
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.
 
You may zero-length String "" (which you can't see, the same with Null)
rather than Null and ZLS is *different* from Null.
 
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
Click to expand...


.
Click to expand...
 
Back
Top