How do you not show blank fields on a report?

[fkneg1]

I have a report based on a query. Sometimes some of the fields on the report are blank because the information is not available. Is there a way to not show the blank fields on the report and to move the next field up into the space?

I have tried using Is Not Null on the query criteria but if any one field is null it doesn't show any of the others on the report.

I have currently got it so that the height is set to 0 and can shrink = yes and this seems to work but the field is still there (although hidden) - I would rather it was removed completely if it is blank as I am hoping eventually to make the output for each field show on a PowerPoint presentation and I don't want blank slides which I think this solution might do??

 

[cricketbird]

I think you should look at the left and right joins in your query. Right click on one of the arrows between your two tables.

Let's say you have customers, some with orders, some without. Right now, you probably have your link to include all customers and then the orders associated with them, which will generate "blanks" for customers without orders. What you probably want is all orders, and the customers associated with them, which will get you what you want.

 

[Pat Hartman]

Just FYI, there is no way to shift controls around on a form/report unless you completely build the form manually at runtime and that won't be possible if you use the runtime engine. Access will recover vertical space if entire rows are empty but it will not shift controls horizontally. If this is a problem for you, it is almost certain that your table is not correctly normalized. You might want to do some research and consider how to redesign the table.

 

[Mihail]

The same problem in this thread.
And some answers.