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....
[imgnothumb]http://bytes.com/attachments/attachment/6549d1344874930/query.jpg[/imgnothumb]

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

[noparse] 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));[/noparse]

[twinnyfo]

Yes, much more sense. You may want to begin by making sure your query is only selecting those records identified by you CourseCode on the Form. Build you query piece by piece in order to determine when and where (and how) it is returning records you don't want.

[twinnyfo]

Also, the reason you are having problems posting your SQL qithin the tags is because you have the word "Code" within brackets. It is confusing the editor....

[zmbd]

Also, you will have a much easier time of it if you can rename your fields to use only alphanumeric and the underscore.
For example "Field WithSpace" becomes "Field_WithSpac e"
The extra spaces and special charactors can cause you issues... I've ran into them more than once and they're difficult to track down.

@Twinnyfo: Yep... getting those code tags to stick was a tricky wicket!
Removing those spaces would help a ton!

[NeoPa]

I don't know who's responsible for laying the SQL out so well, but whoever it was made it easy to see that the first element (where [Course Code] is checked) is only ANDed with the next element (where [Communicate Comments] is checked for Null). All the other checks are ORed with the result of these two without the benefit of ANDing them with the [Course Code] check. I suspect you need to put a set of parentheses () around all the elements linked together by the ORs and AND the whole set against [Course Code]. It simply involves adding an open '(' after the "AND " on line #12 and a close ')' before the semi-colon ';' on line #24.

Simples!

[zmbd]

Stepped the SQL when I placed the code tags...