Creating Snaking Columns Within a Record in Access 2010

Question

Creating Snaking Columns Within a Record in Access 2010

Anonymous

What's the best way to create a snaking column within a record in a Report in Access 2010? I have a simple name and address database that includes phone numbers. Right now, my text boxes in my report are set up like this:

Company

Name

Address 1

Address 2

Address 3

City, State Zip

Office Phone

Office Fax

Cell

Home Phone

I want it to snake so that the 4 phone number text boxes print on the right side of the page, across from Company, Name, Address 1 , and Address 2 (not below the City, State Zip). In other words:

Company Office Phone

Name Office Fax

Address 1 Cell

Address 2 Home Phone

Address 3

City, Sate Zip

I have a horizontal line drawn in my report separating the records; so I don't want the records themselves to snake, just the text boxes within those records.

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

8 answers

Answer 1

Thank you to both you and Scott for offering solutions. Thought I'd try both of your suggestions; but found out just how green I really am when it comes to Access. I got totally lost. Gave up and ended up leaving the 4 phone number text boxes as they were (beneath the City, State, Zip). That will work for what I need it to do.

Answer 2

You'll find an example of the sort of concatenation over multiple lines which Scott describes in Concat.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

In this little demo file the option to concatenate values as 'address' lines opens a report in which a single text box uses a slightly modified version of the CanShrinkLines function published by Microsoft. This suppresses Nulls so that no blank lines appear in the concatenated string expression. The report in my demo includes only the one text box; yours would have two side by side, the ControlSource of each calling the CanShrinkLines function, into which the relevant columns are passed. In my case the concatenation is done in the report, but it could equally well be done in a query as Scott describes.

Answer 3

You didn't mention Shrink and Grow initially.

You have 2 options. Option 1 would be to use a subreport for the phone numbers. but that may also run you a foul of Shrink and Grow. The other would be to concatenate. In the Query behind the report, have 2 columns, one for the report, the other for the phone numbers. Concatenate each set into each column and have one 2 textboxes on the report bound to the concatenated columns.

Answer 4

Anonymous

I've tried that. In fact, that's what I started with. But if one or any of those fields contains no data (either on the left side or the right side), it gives me blank lines, even though I've got the controls set to Can Shrink Yes.

0 comments

Answer 5

ScottGem 68,810 Volunteer Moderator

This is not a snaking issue. What you need to do is arrange your controls so the phone number controls are on the right side of the Detail band. Simply drag and drop the controls into place.

0 comments

Share via

Creating Snaking Columns Within a Record in Access 2010

8 answers