Query/Report Behavior?

[Todd D. Levy]

I have a query based report where a number of the fields in each record
are Yes/No data fields.

I would like this report to behave as follows:

If ALL of the Yes/No data fields in a given record have a value of YES,
I would like that particular record to be suppressed (I.e. NOT appear in
the report).

and

If ANY of the Yes/No data fields in a given record have a value of NO, I
would like that particular record to appear in the report.

How can this be done?

[Allen Browne]

Todd, the structure you describe usually represents something that has not
been normalized properly, i.e. you should create a related table with a
record for each choice that does apply instead of heaps of check boxes in
one record.

To work around this structure, you could use the fact that Access uses -1 of
True and 0 for False. Therefore summing the fields gives you a count of how
many are checked. Type the expression into the Field row of query design:
HowMany: Abs([Field1] + [Field2] + [Field3])
Then in the Criteria row, ask for fewer than the total number of boxes you
have:
< 3
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Todd D. Levy" <us**************@spamex.com> wrote in message
news:MP************************@nyc.news.speakeasy .net...

I have a query based report where a number of the fields in each record
are Yes/No data fields.

I would like this report to behave as follows:

If ALL of the Yes/No data fields in a given record have a value of YES,
I would like that particular record to be suppressed (I.e. NOT appear in
the report).

and

If ANY of the Yes/No data fields in a given record have a value of NO, I
would like that particular record to appear in the report.

How can this be done?

[Todd D. Levy]

Allen,

Thanks for the help; I am going to use your work around solution because
changes to the structure of the database are not possible at this time.

One question: does using Abs in the field row of the query treat the -1
for Yes/True as 1 so that the sum of 3 fields is 3 and not -3?

I would like to do one other thing for this report:

On the report itself I would like a Yes/No field with a Yes value to be
displayed with GREEN text, and a Yes/No field with a NO value to be
displayed with RED text.

I assume this is done with VBA code; does it have to be done on a field
by field basis individually, or is it possible to address this issue for
all Yes/No fields with a single block of code?

In article <40***********************@freenews.iinet.net.au >,
Al*********@SeeSig.Invalid says...

Todd, the structure you describe usually represents something that has not
been normalized properly, i.e. you should create a related table with a
record for each choice that does apply instead of heaps of check boxes in
one record.

To work around this structure, you could use the fact that Access uses -1 of
True and 0 for False. Therefore summing the fields gives you a count of how
many are checked. Type the expression into the Field row of query design:
HowMany: Abs([Field1] + [Field2] + [Field3])
Then in the Criteria row, ask for fewer than the total number of boxes you
have:
< 3

[Allen Browne]

Yes Abs() drops the sign, returning the absolute value.

If you represent the control with a text box on your report, you could use
Conditional Formatting to set its BackColor. In report design view, select
the text box, and choose Conditional Formatting from the Format menu.

Conditional Formatting is not available in Access 97 and older, but you can
still include the color in the Format property of the text box.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Todd D. Levy" <us**************@spamex.com> wrote in message
news:MP************************@nyc.news.speakeasy .net...

Thanks for the help; I am going to use your work around solution because
changes to the structure of the database are not possible at this time.

One question: does using Abs in the field row of the query treat the -1
for Yes/True as 1 so that the sum of 3 fields is 3 and not -3?

I would like to do one other thing for this report:

On the report itself I would like a Yes/No field with a Yes value to be
displayed with GREEN text, and a Yes/No field with a NO value to be
displayed with RED text.

I assume this is done with VBA code; does it have to be done on a field
by field basis individually, or is it possible to address this issue for
all Yes/No fields with a single block of code?

In article <40***********************@freenews.iinet.net.au >,
Al*********@SeeSig.Invalid says...

Todd, the structure you describe usually represents something that has not been normalized properly, i.e. you should create a related table with a
record for each choice that does apply instead of heaps of check boxes in one record.

To work around this structure, you could use the fact that Access uses -1 of True and 0 for False. Therefore summing the fields gives you a count of how many are checked. Type the expression into the Field row of query design: HowMany: Abs([Field1] + [Field2] + [Field3])
Then in the Criteria row, ask for fewer than the total number of boxes you have:
< 3