I am importing a lot of data from an Excel table into mostly memo fields in Access. Is there any way to carry paragraph returns and formatting over into Access?

Thanks!

Jean

Formatting, no. Paragraph returns, yes. The problem is that Excel uses (I believe) only Chr(10) (LF), whereas Access uses Chr(13) & Chr(10) (CR and LF). Import the data, then run an Update query that uses the Replace function to change all occurrances of Chr(10) to Chr(13) & Chr(10):

UPDATE MyTable SET MyField = Replace([MyField], Chr(10), Chr(13) & Chr(10))

Thanks! I'm such a newbie, though, that I don't exactly know how to run an update query. Is it easy to describe, or can you point me in the right direction?

Thanks again!

Jean

Create a new query. When prompted, select the table you just imported and click Close on that dialog. Drag the field that contains the text with paragraphs from the list into the gird. Change the query into an Update query (look under the Query menu for the choices). When you do that, a new row ("Update To") will appear in the grid. Type

Replace([MyField], Chr(10), Chr(13) & Chr(10))

into that row under the field name. (Replace "MyField" with the name of the actual field)

Run the query, and you should be done.

(SUGGESTION: Do this on a copy of your data, just in case something goes wrong!)

Thanks, Doug. I did what you suggested and it seemed to work; however, I don't see any difference in the actual table (I presume I'd have to put it in a report to see the paragraph returns?) Am I missing something--I feel dumber and dumber by the minute. Access does that to one, I'm told!

Cheers,
Jean

It should be noticable in a report.

It's possible my memory is going, ad Excel uses CR (Chr(13)) rather than LF (Chr(10))

See whether

Replace([MyField], Chr(13), Chr(13) & Chr(10))

works any better.

(You'll need to do this on the original data, not the data you've already modified)

Nope, your memory is spot on, Doug.

Excel definitely uses Chr(10), or vbLF, to add a new line in a cell, when the cell is formatted to 'Wrap Text'. The manual key sequence is Alt-Enter.

If you were to do something like this:

http://www.utteraccess.com/forums/showthre...?Number=1423318

You could tweak that code to something along the following lines.


*untested - air code*

vbCrLf would another valid VB constant you could replace vbLf with.