Problem using zero height and can grow in report

[Bobnz]

I need to remove rows that hold no data from a report.
I thought if I used zero height for the controls on the report and set can grow to yes that the controls would only be visible and take up space if they held data but....

This is what the report looks like now with 0 height and can grow

Label col1 col2 col3 col4
x
x
x
x
x

each row actually takes up 5 rows
and it needs to look like

Label col1 col2 col3 col4
x x x x x

these fields are from one row of the form which is based on a table .
the underlying table doesnt have generic fields like 'quantity' or 'comment' but individual quantities for each product

ProductA, ProductB as opposed to Product

ProductAQuantity, ProductBQuantity as opposed to Quantity

each product has one row on the form with the products specific quantity, comment, orderdate, color fields on the same row.

the report is based on a select all query of the underlying table using the unique jobid that matches the jobid on the form, the report consists of textboxes and labels bound to the appropriate fields. works fine but is to big for 1 A4 sheet so need to remove rows that are not required for the design.

Messy I know but it works.

B

 

[GingerR]

what is your sql statement for the report's recordsource? Please post that and all related sql queries and table structure. Thanks.

 

[Bobnz]

this is recordsource for report.

SELECT tblworksheet.KCjobnumber, tblclient.jobnumber AS tblclient_jobnumber, tblclient.clientname, tblclient.dliveryaddress, tblclient.homeph, tblclient.workph, tblclient.delivtimeonsite, tblclient.dispatchdate, tblclient.builder, tblworksheet.jobnumber AS tblworksheet_jobnumber, tblworksheet.breakfastbtop, tblworksheet.bbtquantity, tblworksheet.bbtcomment, tblworksheet.bbtodate, tblworksheet.laminatecolor, tblworksheet.lcquantity, tblworksheet.lccomment, tblworksheet.lcodate, tblworksheet.edgedetail, tblworksheet.edquantity, tblworksheet.edcomment, tblworksheet.edodate, tblworksheet.thickness, tblworksheet.thquantity, tblworksheet.thcomment, tblworksheet.thodate, tblworksheet.upstandheight, tblworksheet.usquantity, tblworksheet.uscomment, tblworksheet.usodate, tblworksheet.topjoiners, tblworksheet.tjquantity, tblworksheet.tjcomment, tblworksheet.tjodate, tblworksheet.doorcolor1, tblworksheet.pvc1, tblworksheet.dc1supplier, tblworksheet.pvc1supplier, tblworksheet.dc1quantity, tblworksheet.pvc1quantity, tblworksheet.dc1comment, tblworksheet.pvc1comment, tblworksheet.dc1odate, tblworksheet.pvc1odate, tblworksheet.doorcolor2, tblworksheet.pvc2, tblworksheet.dc2supplier, tblworksheet.pvc2supplier, tblworksheet.dc2quantity, tblworksheet.pvc2quantity, tblworksheet.dc2comment, tblworksheet.pvc2comment, tblworksheet.pvc2odate, tblworksheet.dc2odate, tblworksheet.carcaseedgetapecolor, tblworksheet.cetthickness, tblworksheet.cetlmetres, tblworksheet.cetodate, tblworksheet.spraypainter, tblworksheet.spquantity, tblworksheet.spcomment, tblworksheet.spodate, tblworksheet.drawertype, tblworksheet.dtquantity, tblworksheet.dtcomment, tblworksheet.dtodate, tblworksheet.benchheight, tblworksheet.bhquantity, tblworksheet.bhcomment, tblworksheet.bhodate, tblworksheet.toespaceheight, tblworksheet.tshquantity, tblworksheet.tshcomment, tblworksheet.tshodate, tblworksheet.floorfinish, tblworksheet.ffquantity, tblworksheet.ffcomment, tblworksheet.ffodate, tblworksheet.topofcubs, tblworksheet.tocquantity, tblworksheet.toccomment, tblworksheet.tocodate, tblworksheet.handlesoff, tblworksheet.hoquantity, tblworksheet.hocomment, tblworksheet.hoodate, tblworksheet.handleposition, tblworksheet.hpquantity, tblworksheet.hpcomment, tblworksheet.hpodate, tblworksheet.sinkbowl, tblworksheet.sbquantity, tblworksheet.sbcomment, tblworksheet.sbodate, tblworksheet.tap, tblworksheet.tquantity, tblworksheet.tcomment, tblworksheet.todate, tblworksheet.wastedisposal, tblworksheet.wdquantity, tblworksheet.wdcomment, tblworksheet.wdodate, tblworksheet.oven, tblworksheet.oquantity, tblworksheet.ocomment, tblworksheet.oodate, tblworksheet.hob, tblworksheet.hquantity, tblworksheet.hcomment, tblworksheet.hodate, tblworksheet.splashback, tblworksheet.spbquantity, tblworksheet.spbcomment, tblworksheet.spbodate, tblworksheet.rangehood, tblworksheet.rhquantity, tblworksheet.rhcomment, tblworksheet.rhodate, tblworksheet.microsize, tblworksheet.msquantity, tblworksheet.mscomment, tblworksheet.msodate, tblworksheet.fridgesize, tblworksheet.fsquantity, tblworksheet.fscomment, tblworksheet.fsodate, tblworksheet.dishwasher, tblworksheet.dwquantity, tblworksheet.dwcomment, tblworksheet.dwodate, tblworksheet.accessdoorway, tblworksheet.adquantity, tblworksheet.adcomment, tblworksheet.adodate, tblworksheet.rubbishbin, tblworksheet.rbquantity, tblworksheet.rbcomment, tblworksheet.rbodate, tblworksheet.gdsize, tblworksheet.gdsupplier, tblworksheet.gdcomment, tblworksheet.gdodate, tblworksheet.rollerdoor, tblworksheet.rdquantity, tblworksheet.rdcomment, tblworksheet.rdodate, tblworksheet.specialhware, tblworksheet.shquantity, tblworksheet.shcomment, tblworksheet.shodate, tblwor
ksheet.lazysusan, tblworksheet.lsquantity, tblworksheet.lscomment, tblworksheet.lsodate, tblworksheet.furnituretruck, tblworksheet.ftquantity, tblworksheet.ftcomment, tblworksheet.ftodate, tblworksheet.plumber, tblworksheet.pquantity, tblworksheet.pcomment, tblworksheet.podate, tblworksheet.electrician, tblworksheet.equantity, tblworksheet.ecomment, tblworksheet.eodate, tblworksheet.accessories, tblworksheet.aquantity, tblworksheet.acomment, tblworksheet.aodate
FROM tblclient INNER JOIN tblworksheet ON tblclient.jobnumber = tblworksheet.jobnumber;


how do I export table structure? I can't cut and paste in design view.