Ms Access 2007 text box formatting problem

I have a text box on a MS Access report.
The source is a memo field in a table that I have imported from a SQL Server database.

When I display the field in a report none of the formatting is preserved, instead all the text runs together in one line.
How can I fix this so it preserves line breaks and carriage returns?

Thanks
Suzanne
smurray20Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Have you set the report control;s Text Format property to RichText?
0
smurray20Author Commented:
Yes, have tried this but it doesn't make any difference.
Any other ideas?
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If you view the data on a Form, is it formatted correctly?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

smurray20Author Commented:
yes
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Did you check that the RichText property is set in both the Table and the Report?
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I have a text box on a MS Access report.
Try using a RichTextBox control instead of a textbox.  
From Report Design, Toolbox toolbar, click on the toolbox icon with three dots, then scroll down to 'Microsoft RichTextBox (some version)'.
Left-click, hold, and drag like you were adding a textbox.
Once it's set, do a right-click:Properties, and set the Control source.
0
smurray20Author Commented:
Will those work if I have to convert the database to a 97-2002 compatible one?
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The only way to show RichText in older versions is to do as jimhorn suggests and use an ActiveX control. Even then, you can't use those in Reports.

Stephen Lebans has a fix for that: http://www.lebans.com/richtext.htm

Or you can use the FMS Total Access Memo: http://www.fmsinc.com/MicrosoftAccess/memo.html
0
smurray20Author Commented:
I actually managed to solve the problem myself!

= Replace([myfield],Chr(13), Chr(13))

I'm not sure why this worked but it did!

Thanks for trying to help though!
Suzanne
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
smurray20Author Commented:
How do I close the question?
0
silkdCommented:
I am having the same problem on my report.  I tried using a RichTextBox control and that worked, but I do not see an option for "Can Grow" and "Can Shrink" on the RichTextBox control.  I do not understand what was meant in the solution: = Replace([myfield],Chr(13), Chr(13))
I put this in the Format property in the Textbox, but all it did was truncate the text.  Any help?  Thanks.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>Any help?
Certainly.  Ask it as your own question, instead of piggy-backing on one that hasn't been commented on since January.
That way, all experts will see it.
This way, only the experts that commented in this question will see it, and since there are no points up for grabs, motivation is somewhat low.
0
Ritz93Commented:
What this means that you can write a VBA script to replace the chr(13). It worked for me with a modified version. i.e. = Replace([myfield],Chr(13), Chr(13) & Chr(10)).

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.