return fields not null

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

Guest

I have a memo field [comments] in a table. I am creating a query and I want
to return only the records where [comments] is not null.

I put Is not Null in the criteria of the query but it is still return all
records.

Is there a special way to accomplish this for a memo field?
 
Unfortunately that didn't work either.

This is on a linked table that may be causing the problem.
--
Thanks in advance
Stefan


David Billigmeier said:
Try placing <>"" in the criteria field.


--
Regards,
Dave


Stefan said:
I have a memo field [comments] in a table. I am creating a query and I want
to return only the records where [comments] is not null.

I put Is not Null in the criteria of the query but it is still return all
records.

Is there a special way to accomplish this for a memo field?
Click to expand...
Click to expand...
 
Linked tables shouldn't make a difference. Have you tested to see that you
are actually producing NULLs on the fields in question?

Randall Arnold

Stefan said:
Unfortunately that didn't work either.

This is on a linked table that may be causing the problem.
--
Thanks in advance
Stefan


David Billigmeier said:
Try placing <>"" in the criteria field.


--
Regards,
Dave


Stefan said:
I have a memo field [comments] in a table. I am creating a query and I want
to return only the records where [comments] is not null.

I put Is not Null in the criteria of the query but it is still return all
records.

Is there a special way to accomplish this for a memo field?
Click to expand...
Click to expand...
Click to expand...
 
How do you check that?


--
Thanks in advance
Stefan


Randall Arnold said:
Linked tables shouldn't make a difference. Have you tested to see that you
are actually producing NULLs on the fields in question?

Randall Arnold

Stefan said:
Unfortunately that didn't work either.

This is on a linked table that may be causing the problem.
--
Thanks in advance
Stefan


David Billigmeier said:
Try placing <>"" in the criteria field.


--
Regards,
Dave


:

I have a memo field [comments] in a table. I am creating a query and I want
to return only the records where [comments] is not null.

I put Is not Null in the criteria of the query but it is still return all
records.

Is there a special way to accomplish this for a memo field?
Click to expand...
Click to expand...
Click to expand...
 
Sound to me zero-length String is allowed in your Field.

The safer way to test in this case is to create a Calculated Field:

FldLen: Len(Trim([YourField] & ""))

and set the criteria as
0
Click to expand...

in this calculated Fields.

--
HTH
Van T. Dinh
MVP (Access)



Stefan said:
I have a memo field [comments] in a table. I am creating a query and I want
to return only the records where [comments] is not null.

I put Is not Null in the criteria of the query but it is still return all
records.

Is there a special way to accomplish this for a memo field?
Click to expand...
 
Stefan said:
I have a memo field [comments] in a table. I am creating a query and I
want
to return only the records where [comments] is not null.

I put Is not Null in the criteria of the query but it is still return all
records.
Click to expand...

Do you have any OR logic? Could you perhaps post the SQL view of your query?

John W. Vinson/MVP
 
You could do it with a VBA module that iterates through all records and use
debug.print to display the values for each record of the field in question.
Print out a message telling you it equates to NULL if true so you can see if
you have any. make sure you test for the empty string ("") also.

Or the quick and dirty method is run the query and examine the fields in
question. If there are no blanks at all, you're never satisfying the
criteria for NULL or "".

Randall Arnold

Stefan said:
How do you check that?


--
Thanks in advance
Stefan


Randall Arnold said:
Linked tables shouldn't make a difference. Have you tested to see that you
are actually producing NULLs on the fields in question?

Randall Arnold

Stefan said:
Unfortunately that didn't work either.

This is on a linked table that may be causing the problem.
--
Thanks in advance
Stefan


:

Try placing <>"" in the criteria field.


--
Regards,
Dave


:

I have a memo field [comments] in a table. I am creating a query and I want
to return only the records where [comments] is not null.

I put Is not Null in the criteria of the query but it is still return all
records.

Is there a special way to accomplish this for a memo field?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top