If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Hide unpopulated fields
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! |
#2
|
|||
|
|||
Hide unpopulated fields
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]. -- Duane Hookom MS Access MVP "Newsgroups" wrote in message ups.com... 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! |
Thread Tools | |
Display Modes | |
|
|