Close up gaps in Reports due to 'empty' fields

[Soton]

I have a form which includes a block of data with a number of label/textbox combinations where the label is the item and the text box is the quantity. When I produce a report based on the Form, I want to hide the label/textbox combination where the textbox is empty (which I can do) but I don't want any gaps in the list on the Report.

For example, I textboxes 2 and 4 were empty I don't want the Report to show label/textbox 1, a gap (for Row 2), label/textbox 3, a gap (for Row 4), label/textbox 5, label/textbox 6 etc. I want the Report to show label/textbox 1 on row 1, label/textbox 3 on row 2, etc. Note that the label and textboxes show on the Report with borders (so it looks like a grid).

I could try reading the data into a two-dimensional array, excluding the empty items, then print the array out but I am not sure how to format the printout in code to give 'boxes'.

Any ideas.

I am using Access 2002 running under Windows XP

 

[gemma-the-husky]

best way is to try to base the report on a similar query to the form's query, but where you can just put a >0 criteria in appropriate places, to avoid the values in the first place

are you printing the form as a report - its not recommended for issues like this - ok as a rough and ready but hard to manage

the other way, - in the report, i assume these things are in the detail section - now either in the format or print event for the detail section, you can test the rogue values, and cancel the print for that line. not sure offhand exactly what the syntax is, but it definitely works

 

[Soton]

The report is based on a query with labels/textboxes on the Report positioned (with left and top coordinates) to suit all the entries in the query. If I use the query to select only those with values in the textbox, won't I get an error message when the report opens as some of the fields will be missing.

Its complicated a bit in that this data block is not the only data being printed in the report so if I do something which misses lines in printing won't I lose the page formatting. Its complicated further in that there are about 20 combinations of label/textbox in two columns of two (reading down one pair of columns then the other pair) so missing lines might give a peculiar output. I was hoping to find a method with the single pair column and adapt it - i.e. start with something simple!!

The intention was also to preview exactly what would be printed on screen first.

 

[gemma-the-husky]

just try different things - open the reports in preview mode, so you dont use paper unnecessarily, until you get the layout you want

if you want to keep the space for empty boxes. but not show then on the report, you can make them visible=false in the format event.

 

[datAdrenaline]

Also, make you you set the CanShrink/CanGrow properties of the text box contols to Yes. Also do that for the section of the Report that contains those controls.

But, one thing to note, a Label control does not have CanGrow/CanShrink properties, but when a label goes hidden, most of the time its effective shrunk with the control it was tied too. Even with that, I will sometimes use a Text Box control for my label with an expression that looks like this:

=fLabelIt("MyLabelText",[TextBoxControlNameBeingLabeled] & "")

Then in a Standard Module (or the code behind the Report) I have the function ...

Private Function fLabelIt(strLabelText, strValue) As String
    If Len(strValue) > 0 Then fLabelIt = strLabelText
End Function

By using a Text Box control as the label, you can use the CanGrow and CanShrink properties..

....

Hope that helps!