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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Using Access 2000 sorting in a query to eliminate blank text fields.
Criteria "Is Not Null" does not eliminate blank fields. Why?
 
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?
 
Much thanks! <>"" produced good results. Problem solved. (but I still
winder why not null didn't work.)

Ken Snell (MVP) said:
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>

Mike33 said:
how do I handle blank text fields which do not allow zero lenght strings?
Click to expand...
Click to expand...
 
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>


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

Ken Snell (MVP) said:
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>

Mike33 said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top