return fields not null

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?
 
G

Guest

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?
 
G

Guest

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?
 
G

Guest

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?
 
V

Van T. Dinh

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

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?
 
J

John W. Vinson/MVP

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.

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

John W. Vinson/MVP
 
G

Guest

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top