how do i break a column of text into 2 columns? or cant i?

[Wozzzzza]

i have an access 97 database where i have people enter text and it gets printed out onto a report with 2 columns.
sometimes people enter a long column of text, maybe 40 lines, and when it gets printed on a report if there is already some text from a previous person printed on the elft column the 40 or so lines from the next person is too long to fit on the page and gets sent to the second column on the report which in turn leaves the last half of the column on the left hand side blank.
is there anyway to break a long column of text into 2 so half of it takes up the complete column on the left and the rest is put int he column on the right half of the page?? see attachment for example.

 

[JamesMcS]

You could make a query do this - look up left, right and len as functions in Access help. So, if you wanted to split it in half, you could put

Col1:left([fieldname],len([fieldname])/2)

in Column 1, and

Col2:right([fieldname],len([fieldname])/2)

in Column 2. You might have to look at rounding the number than len brings back, just in case it's not an even number.

 

[JamesMcS]

Or just format the report to show two columns, of course... it's in page setup

http://www.databasedev.co.uk/multi-column_report.html

 

[Wozzzzza]

You could make a query do this - look up left, right and len as functions in Access help. So, if you wanted to split it in half, you could put

Col1:left([fieldname],len([fieldname])/2)

in Column 1, and

Col2:right([fieldname],len([fieldname])/2)

in Column 2. You might have to look at rounding the number than len brings back, just in case it's not an even number.

i dont have any idea what you mean or how i could implement this

Or just format the report to show two columns, of course... it's in page setup

http://www.databasedev.co.uk/multi-column_report.html

it is formatted as 2 columns but if a long row wont fit in one column it moves the entire record to the next column which i dont want.

I have one query that spits out a series of records, some like in picture above are quite long and need to be split over 2 columns.

 

[JamesMcS]

Apologies - you'll want to create a query to base your report on. Drag down all the fields you want to see in the report (except the field we're talking about here), then copy what I've put above into two empty columns in the query design - obviously substitue the name of the too-long field for [fieldname].

If you read up about left, right and len functions in the help file it'll explain everything.

 

[Wozzzzza]

ok i understand it now, but how can this be implemented in a 2 column report to split it between columns? say for instance i have 13 two line items and this long one that wont fit on the first column and needs to be split to be on second column

 

[JamesMcS]

You could use iif to determine the length of the string, then from there do whatever you like really - another journey into the help file

 

[Wozzzzza]

yeah i know how to use that statement but cant think how to implement it to break up a text field over 2 columns

 

[JamesMcS]

OK - so the text you want to cut between two columns is all in one field yeah? So in your report that's based on the query above, just have one big text box bound to 'Col1' and another one bound to 'Col2'.

Your iif statement would have to say "If the field is above a certain length, split into two columns. If it's below a certain length, don't"

This isn't going to work if it goes over 2 columns though - I think what you need to do is think about redesigning the table, so that rather than multiple users appending data to one field, make it so that each user update occurs in a new record. Then, if you need to, you can just concatenate the multiple records into one.

 

[DCrake]

Hate being a killjoy, but for 1 it is unlikely that you will be able to do this and 2 what happens when you get to the end of column 2 and there is still outstanding text? Oh and if you do split the text into two parts you need to be able to detect spaces because you may end up splitting the text half way through a word.

 

[JamesMcS]

Yeah I thought of that in an earlier post, there'd have to be some rounding and string comparison going on too, and there would be a problem in the event of more than 2 columns.

It does seem a strange way to do this, I'd be interested to hear the OP's thoughts on changing the structure of the table.

 

[Wozzzzza]

people do append a new record when they enter new things, but people enter long lists
at this point in time i think its more hassle than its worth to split a column. I will see how this database evolves and if there are any issues how it is.
thanks.