Hide unpopulated fields

[Newsgroups]

Hi and thanks for the help. I am using Access 2003. I have a table with
5 fields. Field 1 is always populated. Fields 2-5 are sometimes
populated and sometimes blank. On the report I would like to print only
the populated lines.

For an example: Record 1 has data in Fields 1, 2, and 3. The report
will print those fields but totally exclude Fields 4 and 5--no labels
will print, no empty space will print.

There are 945 records in the table. Here are the field names if that
helps.
Field 1 = Name
Field 2 = Advertising
Field 3 = Transportation
Field 4 = Request
Field 5 = Transcript

They are all text fields with 255 characters.They are not indexed.

I did a search on this topic but did not understand the suggestions
given. If the answer has to do with a macro or VBA could you give
specific directions please. Thanks!

 

[Duane Hookom]

Your table structure sound un-normalized...
I would add an autonumber primary key (ID) to the table. Then create a union
query like:

SELECT ID, [Name] as fld, 1 as SortOrd
FROM tblWith6Fields
WHERE [Name] is not Null
UNION ALL
SELECT ID, [Advertising], 2
FROM tblWith6Fields
WHERE [Advertising] is not Null
UNION ALL
SELECT ID, [Transportation], 3
FROM tblWith6Fields
WHERE [Transportation] is not Null
UNION ALL
SELECT ID, [Request], 4
FROM tblWith6Fields
WHERE [Request] is not Null
UNION ALL
SELECT ID, [Transcript], 5
FROM tblWith6Fields
WHERE [Transcript] is not Null;

Then create a main report based on your tblWith6Fields and a 5 column
subreport based on the union query. Sort the subreport on the ID and SortOrd
fields. Add the subreport to the detail section of the main report and set
the Link Master/Child properties to [ID].