Restoring line breaks in memo field

[Gina]

I am running Access 97. I have imported a memo field from an Excel
spreadsheet. In the Excel sheet the field had multi lines with
carriage returns at the end of each line. Having imported the data,
the Access memo field has joined all the lines together - where the
carriage returns were, there are now little square box symbols.

In the form which displays the memo field, the square boxes are
replaced by thick black vertical lines. Overall, the effect is ugly.

How can I restore the line breaks?

Gina

 

[Gina]

Access and Excel use different new line sequences.  Not sure
but I rhink Excel is LF (10) while Access uses CR LF (13 10)
You can fix it by running an Update query, but you have to
be careful to not update the same record twice.

First make a backup of the table so you can recover if
things get messed up.  Then try using a query like:

UPDATE thetable
SET memofield = Replace(memofield, Chr(10), Chr(13) &
Chr(10))
WHERE memofield Not Like "*" & Chr(13) & "*"

Hi Marshall,

Thanks for that. Since Access 97 has no inbuilt Replace function, I
used an Allen Browne customised replace function, which, combined with
your query did the job perfectly.

Many thanks indeed.

Gina