Display only the checkboxes with "Yes" on my report

[JNariss]

Hello,

I have a large database with many tables that consist of Yes/No data
types. I figured out that by selecting the Lookup tab and changing the
Display Control from Check Box to Text Box it will display the word
"Yes" or "No" - which is what I want.

Now...........I am trying to create a report that will also display the
words BUT only the ones that are equal to "Yes". If it is equal to "No"
then it should not show up on the report.

I tried to create a query with all the fields from one table and as the
criteria I put the word "Yes" however, when I ran the query, no records
would show b/c some fields contained the word "No".

Did I do something incorrect when I created the query?? OR is there
another way to go about this??

Thanks,
Justine

 

[Duane Hookom]

How about providing some sample records and desired display in your report?
We only need to see about 6 records and 6 fields. It isn't clear whether you
want records or just specific controls to not show.

 

[JNariss]

Thanks for the quick reply - I will try to explain more clearly.....

All I need to do is display the records with a value of yes on my
report.


Here is how my database works:

Currently I have a report that consists of 4 tables: Employee
Information, Main Menu Options, LogPro Departments and ACP Codes.

Basically our Human Resources (HR) department hires a new employee and
fills out the Employee Information form. This information goes into the
database. Within that form is a section for the HR department to check
off security requests for the new employee. This is where the other 3
tables come into play. On the Employee Information form, the HR
department picks and option from the Main Menu Options which opens up
the LogPro Departments - allowing them to choose a department fand then
the ACP Codes form opens up and they choose a bunch of stuff off this
menu using check boxes.

The ACP Codes table only consists of ESCID Number (which is the
AutoNumber from the Employee Information table - foreign key),
ACPNumber (primary key) and about 30 Yes/No data type field options -
my checkboxes.

So I went into the ACP Code table and changed the Display Control from
"check box" to "text box" so when I view the table I can see either
"Yes" or "No" instead of a box with or without a check.

Then I created a report using all the information from the 4 tables. So
now my report is nearly 3 pages long - way too long; and printing
multiple records will take up a lot of trees.

It is not necessary for me to view the ACP Codes that are unchecked
("No"). I only want my report to show the ACP Codes that the HR
department checked.

 

[Duane Hookom]

A WAG suggests your ACP codes display vertically in the detail section of
your report like:

ACP Code 1
ACP Code 2
ACP Code 3
ACP Code 4
...
ACP Code 30

Some of the above are checked and others not. If only ACP Code 1 and 4 are
checked, you would expect to see:
ACP Code 1
ACP Code 4

Set the control properties to something like:
Name: txtACPCode1
Control Source: =IIf([ACP Field1],"ACP Code 1 Title",Null)
Can Shrink: Yes

I hope you realize if your table was properly normalized, you wouldn't have
this issue. You are storing value as field names.