May 30 2007, 02:21 PM
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?
May 30 2007, 02:54 PM
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))
Jun 1 2007, 06:09 PM
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?
Jun 2 2007, 06:43 AM
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!)
Jun 4 2007, 11:02 AM
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!
Jun 4 2007, 11:12 AM
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))
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)
Jun 4 2007, 05:06 PM
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:
You could tweak that code to something along the following lines.
.Fields(0).Value = Replace$(varArr(i, 1), vbLf, vbNewLine)
*untested - air code*
would another valid VB constant you could replace vbLf with.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here