Blank Rows : Saving Space in a Report

[rfear]

Is there a clever way to get rid of blank rows in a report ?

In simple terms my report contains 2 fields.

Field1 always contains data and therefore I always need a line in my report to display this data.

Field2 does not always contain data. More often it is empty.

Unfortunately, empty or not my report always reserves space on the page to display this field. Therefore my report has alot of blank rows with the occasional text.

How can I get rid of the blank rows and compact my report onto less pages ?

 

[KenHigg]

A simple way would be to concatenate the two fields with vbcrlf in between in the query.

myNewField: myfldOne & iif(IsNull(myfldTwo,"",vbcrlf & myfldTwo)

???

 

[rfear]

Thanks.

I am having trouble with the 'vbCrLf' part of this code. I assume this is trying to introduce a carriage return. Either access assumes it to be text or a field name, either way not a carriage return.

This solution is also pre-empting the report so I could not for example modify the text format between the 2 fields to differentiate the information on the report.

I'll persevere and see what I can come up with.

 

[rfear]

Making some progress by using the OnFormat event in the Details part of the report.

This at least hides or displays rows depending on whether data exists.

Just need to crack the spacing.

 

[rfear]

Cracked it.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.DaysLag > 0 Then
With TextDaysLag
.Height = 230
.Visible = True
End With
With TextLagNotes
.Height = 230
.Visible = True
End With
Me.Detail.Height = 340
Else
With TextDaysLag
.Height = 0
.Visible = False
End With
With TextLagNotes
.Height = 0
.Visible = False
End With
Me.Detail.Height = 0
End If
End Sub

The above routine expands the detail section of the report, sizes the textboxes and makes them visible if there is data to display. Vice versa if there is no data to display. Invoke this routine from the reports OnFormat event and hey presto.

Crude but effective.

 

[Rich]

What's wrong with the inbuilt canshrink/grow property?