Importing Data into a memo field

  • Thread starter Thread starter Don
  • Start date Start date
D

Don

I am really hoping someone can help me here........

I have imported some data from Excel. One of the fields
imported is a memo field. After the import, all my my
line breaks have been lost abnd they have been replaced
with "boxes." There are over 4000 records and I would
rather avoid having to have someone go through them
manually.

Apparently, I cannot use find and repalce to fix this.
Microsoft Knowledge Based Article 210433 shows something
about this.

Is there anway through VBA, I can replace these "boxes"
with a line break inside my memo field? I have serached
through google groups before, but have not found a
solution to my problem.

Any guidance would be appreciated. Thanks in advance.

Don
 
Hi Don,

Excel uses just a linefeed - Chr(10) - as a linebreak within cells,
while Access requires a carriage return as well, Chr(13) & Chr(10) in
that order.

Import the data into Access. Then use the Replace() function in an
update query to add the carriage returns. The Update To expression will
look like this:

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

This works for Access 2002 and later, and for Access 2000 if the current
Office and/or Jet service packs have been installed. For earlier
versions you have to do it slightly differently.
 
Back
Top