Exclude records with no Text

[Guest]

I have a query with numerous fields. Many of the fields are text. I want only
want to query out records that are not blank in each of the fields, so I was
wondering if there was some code I could put in the criteria of each field
that will exclude null or blank values.

Thanks in advance,

 

[John Spencer]

The criteria is
Is Not Null

If you want to exclude records where every one of the fields is blank then
you will need to and the criteria.
If you want to exclude records where one or more of the fields is blank you
will need to or the criteria

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[banem2]

I have a query with numerous fields. Many of the fields are text. I want only
want to query out records that are not blank in each of the fields, so I was
wondering if there was some code I could put in the criteria of each field
that will exclude null or blank values.

Thanks in advance,

Hi,

Use in query criteria for each field

Not Is Null

All in same line to have 'and' filter.

Regards,
Branislav Mihaljev

 

[Guest]

I entered Is Not Null in the criteria for my succession field but it still is
showing records that have blanks in succession. Is this correct?

 

[Guest]

Try this --
Is Not Null And <>""

 

[John Spencer]

Then is sounds as if the field could have blanks or a zero-length
string. Neither of those is null.

You can try the following criteria

Is Not Null and <> ""

If that doesn't work try using a calculated field

Field: Succession: Trim([TableName].[succession] & "")
Criteria: <> ""

That should take care of a field that is all spaces, null, or is a
zero-length field.
'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Guest]

Len(Trim([Field] & "")) > 0

Will return only records where the field is Not Null, is not a Null String
(""), and not one or more spaces.