hide fields in Access 2003

[Guest]

I have run a simple query and want to display only the fields that contain
data in a form or report, but I want that done automatically as I will have
many queries that each will have different fields that have null values. How
do I tell Access to only display the fields that are not null in my
query/form/report? Thank you!

 

[Rick B]

Normally you wouldn't. Explain your table structure to us.

 

[Guest]

Ok, I have a simple table that has a bunch of fields where our employees are
reporting their activities. What I want is a report that only gives me the
fields that an employee has filled in for a certain month, i.e. just the one
record for April, for instance, but not showing any of the fields that didn't
apply that month. Does that make one bit of sense?

What I don't want to do is look at each of the 58 reports I will get each
month and have to manually deselect the "show" box in my query to not display
those fields that are empty. I don't actually care if this is in a report, a
form, or even exported to Word or something. Whatever will save me from this
manual labor will work fine, I think. Thank you!

 

[Rick B]

You still don't tell us how the fields are structured. Will field number 14
always mean "typed letter" and field 15 will always mean "called client"?
Or, will the employee just type any activity they chose into Field 1 and
then the next activity into field 2? In both cases, you are committing
"spreadsheet"! A database is NOT a spreadsheet.



The reason that I ask is that you might have a non-normalized design.
Typically this would be accomplished with two or more tables. One table for
the employee. One table with a list of possible activities and a third
table that would contain the date, the employee, and one activity per
record. An employee that performs 20 activities in a month would have 20
records. An employee that performs 2 activities in a month would have 2
records.

Using this 'normalized' design allows you to easily build your query to pull
only activities that occurred. Actually, if your structure is normalized,
it would be very difficult NOT to build the proper report.

 

[Rick B]

to elaborate a bit, your tables should be...

tblEmployees
--------------
EmployeeNumber
LastName
FirstName
HireDate
Dept
etc.


tblActivities
------------
ActivityNumber
ActivityName


tblMonthlyActivities
--------------------
Date
EmployeeNumber
ActivityNumber

 

[Guest]

I confess that I am indeed committing "spreadsheet" and I humbly apologize.
The database is actually just a bunch of imported spreadsheets, set up to
help us better sort the myriad of data that our employees generate. Field
number 14 is labeled "activity to promote cosmic well-being" and our employee
may type in "rang gong several times" or "thought very deeply about world
peace" depending on his/her activity in that area. Yes, spreadsheet indeed.
What I am thinking is that I may just sort it by contents so that all the
empties fall to the bottom and we can cut that page off. A cheater's
solution, but you can tell the level of expertise you're dealing with here.
Thoughts? Thank you for bearing with me.

 

[Rick B]

You could pull all the records and in your report add a series of UNBOUND
TEXT BOXES. Put the following in it...

=Iif(Isnull([field1]),"","LITERAL TO DISPLAY: " & [Field1])
=Iif(Isnull([field2]),"","LITERAL TO DISPLAY: " & [Field2])
=Iif(Isnull([field3]),"","LITERAL TO DISPLAY: " & [Field3])

Replace the "Literal..." with the 'name of your column heading for field1,
field2, etc.

What this does is...

If field1 is blank, print nothing.

If field 1 has an entry, print the hard coded heading I specify and also
print the activity entered.

If you need them to line up in columns, then make two per line as follows...

=Iif(Isnull([field1]),"","LITERAL TO DISPLAY: ")
=Iif(Isnull([field1]),"",[Field1])



Now, the important part.... set the "CAN SHRINK" value for all of these to
TRUE. This will make sure that any blank fields do not take up space on
your report.


Of course, if you ever change the heading for column #X then you will have
to modify the report as well.

 

[Guest]

Thank you so much. I will work with this and in about 15 years will know what
I am doing, for sure. A