Only display specific data in report

[markswan20]

Hello again apologies for asking 2 questions straight after one another.

I am trying to run a report that will only display data in specific boxes and if the box has no data in ignore it.

I am using code for a single box but my problem i have 10 fields on the form qty 1 - qty 10 now when i view in print preview i have a huge gap between qty 1 and qty 10. This is because qty 1 - qty 9 are my products and qty 10 is my type of installation. Is there a way so that if the other boxes are empty they wont display and qty 10 ends up directly below qty 1.

The code i am using for the single box is

Private Sub Detail_Format(Cancel As Interger, FormatCount As Integer)
If Me.QTY_1 = "0" Then Cancel = True
End Sub

Kind Regards
Mark Swansborugh

 

[CautionMP]

I'm guessing:

Data source:[tt]
QTY_1 QTY_2 QTY_3 ... QTY_10
rec1 4 0 0 7
rec2 0 1 0 4
rec3 3 0 0 6[/tt]

Report:[tt]
Record 1
QTY_1 4
QTY_2 0
QTY_3 0
...
QTY_10 7

Record 2[/tt]

If this is the case you can do the VBA acrobatics (note below) to make the report generate the way you described, I'm thinking it might be easier to change the underlying data source to support the type of Report you are building.

Can you setup the data in a more linear format? i.e.:
[tt]
rec1 QTY_1 4
rec1 QTY_2 0
rec1 QTY_3 0
rec1 QTY_10 7
rec2 QTY_1 0
rec2 QTY_2 1[/tt]

If so you can use grouping to acheive the report format you described, with the report gorwing/shrinking to accomodate the actual data.

Note: You will need to scrutinize the data and based on the values, show/hide each of the 10 text boxes, change the locations of the text boxes that will be displayed on the report, then resize the section of the report to fit the numeber of text boxes displayed.

Instant programmer, just add coffee.