Can Grow makes boxes permanently large throughout entire report

[bridgeo730]

Hello,
This might have a simple answer, but I feel like I've tried everything. I have a report that has a field that can grow and shrink depending on if there is data in it. The problem, though, is that when it grows for one client, it stays that size for all clients. For example, some clients just have one university degree, others have many, and I've set the properties to grow and shrink accordingly. But if John has one degree and Sam has 3, both boxes will be 3 spaces long (and these are not horizontal rows). I am attaching an image here: you can see that because William Tarter has two degrees, Neil Block (above him) also has space for two degrees, when he only has one, creating unnecessary white space. What gives?

[aytee111]

What do you mean by "I've set the properties to grow and shrink accordingly"? A textbox is very small/thin in design, with its CanGrow property set to yes. It seems that that is all you require.

[bridgeo730]

The properties are set to Yes. Can Grow. But my question is one text box (at the end where degrees are listed) setting fixed height for all the other boxes in the report? They should shrink when there's none, and grow when there are many. And they have been set to can grow and can shrink.

[aytee111]

If the textbox that contains the degrees is the same height as the others on the same line then it should stay the same, neither grow nor shrink. Where does that data come from, is there possibly a carriage return in the field that is causing this?

[isladogs]

Make sure all the textboxes and labels on that line are one line high is design view.
They will never shrink less than the design height.

If the controls are in a combined 'frame' delete the frame

[bridgeo730]

Yes they all are. In fact I've even tried shrinking all the box heights on the first line but to no avail. If one text box in the report heightens, all the others heighten regardless if they have content or not.

[bridgeo730]

Aytee111, yes there is a carriage return, but it only returns if there is data. This will look complicated, but here is the data it's pulled from:

Degree: (+[Degree 1 Campus] & " " & [Degree 1 Year] & " " & [Degree 1 Type] & " " & [Degree 1 Major]+Chr$(13)+Chr$(10) & [Degree 2 Campus] & " " & [Degree 2 Year] & " " & [Degree 2 Type] & " " & [Degree 2 Major]+Chr$(13)+Chr$(10)+[Degree 3 Campus] & " " & [Degree 3 Year] & " " & [Degree 3 Type] & " " & [Degree 3 Major])

Still doesn't seem to explain why one text box out of the report dictates the size of the same text box for other clients.

[aytee111]

Maybe you can explain it! How/where does it say only put in a carriage return if there is data?

[bridgeo730]

Since I'm extremely new to this, doesn't the "+" indicate that? (I grabbed that from another site that dealt with an earlier issue). I bet it was in this string the whole time! Is there a way to state IF there is data?

[bridgeo730]

Oh! It turns out in the line I had an & instead of the +

That fixed it. Sheesh. User error. Thank you for having me look at this data. If I tried one more thing in design view, I was going to claw my eyeballs out!

[aytee111]

+ in the middle of the string is the same as & - usually one does not mix and match, & is the preferred way. At the beginning of the string, haven't seen that before, I would assume it would be ignored. There is also Nz (not null) but that would apply to one field, not each set as you have it. Maybe others would do it differently, but I would write it out long-hand, as they used to say!

In fact, it would be far easier to do this in a pre-query - take each of the three sets and make them their own fields, separately. Then in the second query:
IIf(IsNull(Degreefield3),IIf(IsNull(Degreefield2), Degreefield1,Degreefield1 & Degreefield2),Degreefield1 & Degreefield2 & Degreefield3)

[aytee111]

Repost the final solution - we can all learn!

[bridgeo730]

It ended up being an issue with including & between the 2nd and third degrees as opposed to the +. Very small detail!

Degree: [Degree 1 Campus] & " " & [Degree 1 Year] & " " & [Degree 1 Type] & " " & [Degree 1 Major]+Chr$(13)+Chr$(10)+[Degree 2 Campus] & " " & [Degree 2 Year] & " " & [Degree 2 Type] & " " & [Degree 2 Major]+Chr$(13)+Chr$(10)+[Degree 3 Campus] & " " & [Degree 3 Year] & " " & [Degree 3 Type] & " " & [Degree 3 Major]

I bolded the ones that I changed.

[bridgeo730]

It's a long story, but after making a few other changes that had domino effects, I am back where I started with the space issue. I created the first query, which I think has helped. But right now the second query formula you listed has a syntax error that I keep getting. I'm not sure where it's coming from. Also, does your above suggestion include starting a new line with each degree?

Thanks for revisiting.

[aytee111]

Make a query with the PK and with three fields, one each for the degree info:
[Degree 1 Campus] & " " & [Degree 1 Year] & " " & [Degree 1 Type] & " " & [Degree 1 Major]

Then in the second query, add in the c/r:
IIf(IsNull(Degreefield3),IIf(IsNull(Degreefield2), Degreefield1,Degreefield1 & c/r & Degreefield2),Degreefield1 & c/r & Degreefield2 & cr & Degreefield3)

This assumes there aren't any gaps, that if there is something in degreefield2 then there is always something in degreefield1, etc.