carriage return error when importing excel into access

[bird lover]

I work with access and excell 2003. If cell in excell contains data as
follows:
Mary Smith
Long Beach

John James
Los Vegas

Then upon import or linking spreadsheet into access, a little square pops
in. This creates havoc in formatting, when I do a mailmerge between access
and Word.
I don't know programming, vba, sql or anything fancy.

Using an update query for field [people] what is the syntax to lose the
square and replace with appropriate carriage return and/or line fees.

PS. I looked at an article at http://support.microsoft.com/kb/210372 but
access wouldn't accept it.

 

[Ken Snell \(MVP\)]

A "hard return" in an EXCEL cell is the line feed character (Chr(10)). A
"hard return" in an ACCESS field is the combination of the carriage return
and line feed characters (Chr(13) & Chr(10)). The square box you see in the
ACCESS field is the line feed character. What you need to do is run an
update query on the table so that you convert the line feed character to the
combination:

UPDATE YourTableName
SET YourFieldName = Replace(YourFieldName, Chr(10), Chr(13) & Chr(10));

Note that you want to run this update query ONLY ONCE on the table after you
import the data. Otherwise, you'll end up with multiple,consecutuve Chr(13)
characters in your data.