Report that only includes populated fields (1 Viewer)

[john_c]

Hi all. Is there any way to have Access generate a custom report including only the fields that are populated in a form?

I have a form to log customer product quality complaints with a subform that has 20 different fields to record the results of quality control testing of the suspect product. However only a few of the fields will be populated at any one time depending on the product and/or customer. I would like to have a button on the form that will generate a custom report to be issued to the customer with the results of the sample testing. However I only want to include the fields that are populated and have the report formatted accordingly.

Seems like it should be possible but I'm having a hard time making it work. Any ideas?

 

[theDBguy]

Hi. Sounds like you're having this issue because your table structure is not normalized. Can you show us your table design?

 

[john_c]

Thanks for the reply. I have several other reports using the same fields so I don't think there is an issue with the tables. I just need a way to only include fields which are populated and leave out blank ones. However I want the formatting of the report to dynamically adjust depending on the number of fields that are populated. I don't just want the report to include all fields and make the "unpopulated" ones invisible as this will end up looking like a patch-work quilt. For example, the lab may test for density or moisture content on one sample and particle size on another. I want the report for the first one to only show density and moisture content, and the second one only particle size.

 

[theDBguy]

Thanks for the reply. I have several other reports using the same fields so I don't think there is an issue with the tables. I just need a way to only include fields which are populated and leave out blank ones. However I want the formatting of the report to dynamically adjust depending on the number of fields that are populated. I don't just want the report to include all fields and make the "unpopulated" ones invisible as this will end up looking like a patch-work quilt. For example, the lab may test for density or moisture content on one sample and particle size on another. I want the report for the first one to only show density and moisture content, and the second one only particle size.

Are you sure you're table structure is normalized? Can you show us your table design?

 

[john_c]

I have no problem making the report which shows all the fields. I just want some of them to only be added to the report if the field is populated. Don't want a load of empty fields. From what I can find online it may be necessary to create the entire report using VBA. There doesn't seem to be a straight forward method

 

[john_c]

I think I found a method that will do the job:

Thanks for your help @theDBguy

 

[theDBguy]

I think I found a method that will do the job:

Thanks for your help @theDBguy

Hi. Glad to hear you found a solution. Good luck with your project.

 

[john_c]

Strange now, the "Can Shrink" method works when I view the report in Report mode. However when I print or export as pdf the blank fields aren't visible but the "Can Shrink" doesn't appear to work, as in I am left with blank spaces.

 

[theDBguy]

Strange now, the "Can Shrink" method works when I view the report in Report mode. However when I print or export as pdf the blank fields aren't visible but the "Can Shrink" doesn't appear to work, as in I am left with blank spaces.

Hi. Sorry to hear it didn't work.

 

[john_c]

@theDBguy Any idea why it would behave like this? I know the "Can Shrink" method wasn't your solution but I would hazard a guess that you know approx. 1,000,000 times what I do about Access! The report displays perfectly in Report view but this is not replicated in the print or pdf

 

[john_c]

An yet again I found the solution after asking the question:

Its gas, I was googling for 2 hrs this morning trying to find a solution and only found once I started asking here! Thanks for your time anyway!

 

[theDBguy]

An yet again I found the solution after asking the question:

Its gas, I was googling for 2 hrs this morning trying to find a solution and only found once I started asking here! Thanks for your time anyway!

Perseverance pays off. Cheers!

 

[Gasman]

An yet again I found the solution after asking the question:

Its gas, I was googling for 2 hrs this morning trying to find a solution and only found once I started asking here! Thanks for your time anyway!

Thanks for that as I was not aware of the overlapping problem. Never encountered it.