Specifying a Criteria in a Query

[Rosemary]

Hello,

In my query I have included a table that consists of 25 fields. These
fields are all checkbox fields. So the query and the report are not
cluttered, I want the query to show ONLY those fields for each record that
have checkmarks in them.

I tried doing this by putting =TRUE in the criteria for each checkbox field.
But my query then generated no results.

How can I get my query to generate so that each record shows only those
fields where the checkboxes are checked, instead of all 25 fields whether
they are checked or not?

Thanks,

 

[Ken Snell [MVP]]

You might try this (obviously, replace etc. with the other fields):

SELECT * FROM TableName
WHERE ([Field1] + [Field2] + [Field3] + [Field4] + etc. + [Field25]) <> 0;

 

[John Vinson]

Hello,

In my query I have included a table that consists of 25 fields. These
fields are all checkbox fields. So the query and the report are not
cluttered, I want the query to show ONLY those fields for each record that
have checkmarks in them.

I tried doing this by putting =TRUE in the criteria for each checkbox field.
But my query then generated no results.

How can I get my query to generate so that each record shows only those
fields where the checkboxes are checked, instead of all 25 fields whether
they are checked or not?

You cannot. All the records returned by a Query must have the same
"shape" - the same number of fields. Just as with a Table, you cannot
have a Query for which one record has 23 fields, the next has 8
fields, and the one after that has 10.

What you can do instead is put 25 textboxes on the Report (rather than
checkboxes), each bound to one of the 25 yes/no fields in your Table.
Set the format property of each textbox to

"";"Fieldname";"";""

Since a Yes/No field is stored as -1 for True, 0 for False; and since
this type of four-field format property shows a different format for
positive, negative, zero and null values respectively, this will show
the word "fieldname" if the value in the field is True, and blank
otherwise. You'll still have 25 controls on the report, but now the
False values will be blank, and the true ones will show whatever name
you choose. Delete any Labels that the textbox wizard gives you.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps