How do I hide fields with null values on a report?

[Linda]

I have a table with roughly 50 fields that stores different services
available to a customer but not all fields will contain data for a given
record. For example, one customer record may have values in 10 of the 50
possible services and another customer may have values in 25 of the 50
services available.

When I''m printing a work order report for a selected customer I only want
the fields in the table that contain values for that customer....so 10 fields
with values out of a possible 50.

I figured out how to hide the null values but my report looks horrible
becuase I have a value here, a value there, becuase all the null fields are
still taking up a place in the report even though they are not visible.

The fields that have values stored for a particular record are not
sequential and that is throwing me off. Customer A might have values in
field 1, field 5, field 25, and field 50 in the table so I'd like the report
to look as follows:

Customer A
Field 1
Field 5
Field 25
Field 50

 

[Jeff Boyce]

Linda

Your description of the underlying data ("50 fields that store different
services") sounds like a ... spreadsheet! If you really are using repeating
fields to indicate type or category of service, you'll first need to put the
data in a format that allows Access' features and functions to do their best
work.

(I'll point out that with your design, if you ever add or subtract a service
.... or 10 ..., you'll have to change your table, your queries, your forms,
your reports, your macros, your code, ...! This is a maintenance nightmare,
and unnecessary.)

A well-normalized (i.e., relational -- e.g. Access!) table structure would
have one table for customer-only info (FName, LName, PhoneNumber, ...), one
table for Services (one row per each of your "50"), and one table to
resolve/contain valid pairs.

tblCustomer
CustomerID (Primary Key)
FName
LName
PhoneNumber
...

tlkpService
ServiceID (PK)
ServiceTitle
?ServiceDescription
??DateServiceAvailable
??DateServiceDiscontinued
...

trelCustomerService (the "trel" stands for table-relation, i.e., a
"junction"/"resolver" table)
CustomerServiceID (PK)
CustomerID (serves as a foreign key, pointing back to which
Customer)
ServiceID (serves as a foreign key, pointing back to which Service)
?DateServiceAdded
...

Thus, in this data structure, someone with only 10 of the 50 services would
have 10 rows in that "junction" table, while someone else with 30 would have
30 rows. And if you add 10 new services, you simply add 10 rows to the
Service "lookup" table.

This way, when you want a report that lists services, that is a very
straightforward (even wizard-aided) task.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/