return fields not null

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

 

[Guest]

Try placing <>"" in the criteria field.

 

[Guest]

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?

 

[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

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?

 

[Guest]

How do you check that?


--
Thanks in advance
Stefan

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

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?

 

[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

0

in this calculated Fields.

--
HTH
Van T. Dinh
MVP (Access)

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?

 

[John W. Vinson/MVP]

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

 

[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

How do you check that?


--
Thanks in advance
Stefan

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

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?