query search for 'is not null' produces null fields. why?

[Guest]

Using Access 2000 sorting in a query to eliminate blank text fields.
Criteria "Is Not Null" does not eliminate blank fields. Why?

 

[Ken Snell \(MVP\)]

Probably because those fields contain an empty string, not a Null value.

 

[Guest]

how do I handle blank text fields which do not allow zero lenght strings?

 

[Ken Snell \(MVP\)]

If the field's property is set to disallow zero length strings, then my
answer should not be the source of your problem.

So let's start at the beginning. Give us some details about the table, and
about the query. Let's see the SQL string of your query.

Let's try changing the
Is Not Null
to
<> ""
in your query. What results do you get?

Let's also try adding a calculated field to your query with a criterion
expression:
TestLength: Len([NameOfFieldWithProblem] & "")

Criteria: 0

What results do you get?

 

[Guest]

Much thanks! <>"" produced good results. Problem solved. (but I still
winder why not null didn't work.)

If the field's property is set to disallow zero length strings, then my
answer should not be the source of your problem.

So let's start at the beginning. Give us some details about the table, and
about the query. Let's see the SQL string of your query.

Let's try changing the
Is Not Null
to
<> ""
in your query. What results do you get?

Let's also try adding a calculated field to your query with a criterion
expression:
TestLength: Len([NameOfFieldWithProblem] & "")

Criteria: 0

What results do you get?

--

Ken Snell
<MS ACCESS MVP>

how do I handle blank text fields which do not allow zero lenght strings?

 

[Ken Snell \(MVP\)]

Is Not Null did not work because there are indeed empty strings in those
"blank" fields, regardless of the property setting to not allow ZLS values.
Is it possible that the property was changed after these data had already
been entered?

--

Ken Snell
<MS ACCESS MVP>

Much thanks! <>"" produced good results. Problem solved. (but I still
winder why not null didn't work.)

If the field's property is set to disallow zero length strings, then my
answer should not be the source of your problem.

So let's start at the beginning. Give us some details about the table,
and
about the query. Let's see the SQL string of your query.

Let's try changing the
Is Not Null
to
<> ""
in your query. What results do you get?

Let's also try adding a calculated field to your query with a criterion
expression:
TestLength: Len([NameOfFieldWithProblem] & "")

Criteria: 0

What results do you get?

--

Ken Snell
<MS ACCESS MVP>

how do I handle blank text fields which do not allow zero lenght
strings?

:

Probably because those fields contain an empty string, not a Null
value.

--

Ken Snell
<MS ACCESS MVP>

Using Access 2000 sorting in a query to eliminate blank text fields.
Criteria "Is Not Null" does not eliminate blank fields. Why?