SubReports Moving Things around

[slackpipe]

I'm working on a water usage survey. I have 3 tables. One for Peoples names, address etc (tblInfo), one for well information (depth, quality, etc) (tblRes) and one for municipal water (name of the water company)(tblMun). They are all linked together by an ID number and the original report i made worked well. However, in order to fit more on a page, i've been asked to condense it. My report is a main report pulling info from tblInfo. Then i have two subreports pulling from tblRes and tblMun. They are simple subs, one line they print over and over. I've sized them to .1667" and stacked them. then set them to grow or shrink. This worked fine on my original report, but when i place them to the right of the form information, as they grow they push any lines parallel to them down. so instead of

..name.................subreportline1
..address1............subreportline2
..address2............subreportline3

i get

..name.................subreportline1
..........................subreportline2
..address1............subreportline3
..address2...............................



is there anyway to stop this behavior? a workaround? something?

thanx in advance

sp

 

[stopher]

Er I'm not entirely sure I understand what you are asking. However, I think you want a way of dealing with blank lines in addresses (?) and have it so it's not influenced by other fields on a report.

The way to do this is have the full address presented in a single text box (set as can grow). So whatever address is showing in the text box, the box will size correctly and will not be influenced by other fields in the report. The trick is to get the text to look right in the text box.

The way to present the address in the text box is to concatonate (glue) all the address fields together into one long string with carriage returns and line feeds in the appropriate places e.g. =[name] & chr(13) & chr(10) & [Address1]... etc
This is best done in a query and if you widen the row spacing in the query you'll see the address appearing as multiple lines in a single field.

This alone won't sort the problem of dealing with blank lines in an address. We can do this with the following (all as one line in your query - I put it on separate lines for clarity):

FullAddress:
([name]+(Chr(13) & Chr(10))) &
([Addr1]+(Chr(13) & Chr(10))) &
([Addr2]+(Chr(13) & Chr(10))) &
([Addr3]+(Chr(13) & Chr(10))) &
([town]+(Chr(13) & Chr(10)))

What happens here is we are using both + and & as concatonators. The big difference is + will only work if both sides of the + are non-null (hence the rigorous bracketing). So suppose Addr3 is null, then ([Addr3]+(Chr(13) & Chr(10))) will also be null i.e. no CR/LF will be added for this lines and therefore there will be no blank line between Addr2 and Town.

Once you can created your formula in your query you just add a text box in your report with FullAddress as the source and set the can-grow to yes.

If you are going to do this a lot then it might be worth writing an function to do the above or take a look here

hth
Stopher

 

[stopher]

Sorry, I just tried it and realised something. You will need to enlarge the height of the fulladdress text box to the height of your other fields combined. Otherwise you'll have a gap in your other fields.

Also note you can adjust the line spacing in your text box to line up correctly.

Stopher

 

[slackpipe]

The address lines are fine. The problem is when more then one record is returned in the subreport it adds an empty line between the two address lines. The Sub-Report is actually "pushing" the fields in the main report down, even though they are side by side.

 

[stopher]

Hi
The point about the single box still applies. Sorry I muddied the solution with the stuff about blank lines in the address.
Stopher