exclude record if query yields no results

[trixxnixon]

say i want to query a few fields from a table for the purposes of a report. how might i have the query only return records that have data in at least one of the fields to prevent the repot from having many blank pages?

[twinnyfo]

In the criteria of one or more of the fields of your query set it to "Is Not Null". This should return only records that have data in them.

[trixxnixon]

sweet i will try this.

[trixxnixon]

it works, but only if all of the fields i have in the query have data. if one is null the record is excluded. how do exclude only the records where all fields are blank?

[twinnyfo]

For each of the fields you want to check for data, us the "Is Not Null" Criteria, but on a separate cirteria line in the query builder... Please see the attachment....

[zmbd]

The SQL would be somethig like:

Using a table named "Table1"
with [ID_t1] autnumber - pk
with [startdate_t1] as date field
with [something_t1] as text(50)

then

SELECT Table1.ID_t1, 
   Table1.startdate_t1, 
   Table1.something_t1
FROM Table1
WHERE ((Not (Table1.ID_t1) Is Null))
  OR ((Not (Table1.date_t1) Is Null))
  OR ((Not (Table1.something_t1) Is Null));

-z

[trixxnixon]

here is my sql below. it still returns items that were not selected on the form. what might cause this?

[twinnyfo]

What do you mean by "items that were not selected on the form"? I thought this was a query to return only non-all-null records?

[trixxnixon]

I apologize, I was trying to re edit my post when you replied.
I added a column that has a tracking number parameter that is selected from a form. I want the query to return only the records that have the corresponding tracking number, with the subsequent fields. . . with at least one that has content. Null or blank.
Does that make more sense?

[trixxnixon]

I had some problems posting the sql within the tags.

 SELECT [Workshop Survey].[Course Code], 
   [Workshop Survey].[communicate comments], 
   [Workshop Survey].[manner comments], 
   [Workshop Survey].[materials comments], 
   [Workshop Survey].[objectives comments], 
   [Workshop Survey].[participation comments], 
   [Workshop Survey].[objective2 Comments], 
   [Workshop Survey].[prep comments]
FROM [Workshop Survey]
WHERE ((([Workshop Survey].[Course Code])=
   [Forms]![Workshop]![code])) 
   AND ((([Workshop Survey].[communicate comments]) 
      Is Not Null)) 
   OR ((([Workshop Survey].[manner comments]) 
      Is Not Null))
   OR ((([Workshop Survey].[materials comments]) 
      Is Not Null)) 
   OR ((([Workshop Survey].[objectives comments]) 
      Is Not Null)) 
   OR ((([Workshop Survey].[participation comments]) 
      Is Not Null)) 
   OR ((([Workshop Survey].[objective2 Comments]) 
      Is Not Null)) 
   OR ((([Workshop Survey].[prep comments]) Is Not Null));