Blank Lines

[accessewbie]

I have a table that contains data people inputed. Some people didn't answer the question. When I run my report there is a blank space where no answer exists. I have set the property of the box to Yes on Can Shrink and did the same for the Detail Header. Blank lines still exist - what am I missing. Btw my column with the data is named Activity 2 - do I need to set a condition or formula? Please help.

 

[dhookom]

Do you have any other control to the left or right of the text box that you expect to shrink?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[accessewbie]

No. It's the only text box on my report under detail.

 

[dhookom]

So you have only one control in your detail section? Interesting...

Did you also set the section property Can Shrink to Yes?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[accessewbie]

I have the page header.

Then in the detail section I have the control box or source (not sure what it is truly called) - The box is set to yes on Can Shrink and then the Detail Section Header is set to yes on Can Shrink

Then below is the page footer.

Not sure what else to do but I still have the blank lines where someone didn't provide an answer.

 

[dhookom]

You place controls on your forms and reports. One type of control is a text box. There are sections of reports such as Page Header, Page Footer, Detail,...

Some controls and sections have a Can Shrink property. If you set the Can Shrink to Yes for a text box and its value is Null, it will shrink.

What do you see if you set the border of the text box to red? Does the text box control actually shrink?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[accessewbie]

maybe I am asking the question wrong. The answers print in list format (example below). So the blank line exists where someone didn't provide input. Btw I did set the control box to red and no it didn't shrink.

Answer
Answer

Answer

Sorry for my lack of knowledge on this - I'm still struggling as I learn.

 

[dhookom]

Is the value in the answer actually null or is it a zero length string or a space?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[accessewbie]

It is actually Null (nothing was inputed in that row)

 

[dhookom]

Let me see if I understand correctly:
-Your report has a record source (query or table) that has a field named [Answer].
-This field is the control source of the only control that you have in the detail section of your report.
-Your control and the detail section both have their Can Shrink property set to Yes
-If your [Answer] field value is Null, the detail section and the text box do not shrink

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[accessewbie]

Yes that is correct. Have I missed something?

 

[nyzja]

Is the height of your text box the same as the detail section's?

 

[accessewbie]

No it's smaller. Even after adjusting, I still have a blank row where no answer was inputed. I am sure it's a simple solution just something I am overlooking. I've checked every time a suggestion has been made.

 

[traingamer]

Do you have a label for the text box (visible or otherwise)?

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[accessewbie]

Yes the label I have in the page header section.

 

[nyzja]

Could you do a <ctrl><a> on the report and see if perhaps you might have left any other control that you might have made real tiny by mistake?

 

[traingamer]

If there are no other controls in the detail area, you need to verify that the value really is Null rather than an empty string.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[accessewbie]

So how do I really tell if the Value is Null (since this info was imported from excel into access)? And if it turns out the value is not really null but rather an empty string, is there a solution still to eliminate the blank rows?

 

[traingamer]

You can run a query with an expression like exp: Nz([yourfield], "x") - if the field is null, you will see an x

OR

iif(trim([yourfield])="", Null, [yourfield]) will turn an empty string into a null value



Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[accessewbie]

THANKS to all of you for your continued patience and help.

The second suggestion worked when I built a query! So guess I will have to go back and build a query (to eliminate the blank rows) for all the other reports I had already built?