Replace Excel carriage return boxes in Access 2007

  • Thread starter Thread starter goan
  • Start date Start date
G

goan

When I import Excel tables with memo fields into Access 2007, the carriage
returns appear as square boxes.
How do I remove the boxes and retain the formatting.
Since I am a new Access user, I would appreciate easy (step-by-step)
instructions.
Thanks
 
Run an update query on the imported data in the ACCESS table. The query
should use the Replace function to replace Chr(10) (the line feed character,
which is what EXCEL uses to make a new line in an EXCEL cell), with Chr(13)
& Chr(10) (the concatenation of the carriage return and line feed
characters, which is what ACCESS uses to make a new line in an ACCESS
field).

To do this in the query design view:
Open a new query in design view, add the table to the grid, and close the
Add Table window.
Click on the Query Type icon on the toolbar, and select Update Query.
Drag the field that you want to update onto the grid.
In the Update To: cell under that field, type this (change
ActualNameOfTheFieldGoesHere to the real name of the field):
Replace(ActualNameOfTheFieldGoesHere, Chr(10), Chr(13) & Chr(10))
Save the query, then close it.
Make a backup of the database before running the query (in case you don't
like the irreversible results of the query).
Run the saved query.
 
Back
Top