carriage returns

[Guest]

I have imported data form excel to access. However the data in access
contains a littlet square box representing the carriage return. How do i
replace the little square box with a blank. So when i print the report it
doesn't show the little square box but a space.

 

[Guest]

Although this is more of an Excel question then an Access one.....

Highlight the square box and copy it to the clipboard. Then go to
Find/Replace and paste it in for Find and leave Replace blank.

--Steve Huff

 

[Guest]

Thanks for replying. it will not allow me to copy the square box. Where ever
the square box is I need a new line to start. I believe the square box
represent a carriage control. I saw something concerning the replace
function but i am not sure how that works. In excel the user pressed enter
and when i imported it into access the square box appears instead of starting
a new line.

 

[Maryann]

I would think you are just seeing the formatting, not an
actual box. Is it printing?

 

[Dave M]

Take a look at KB 210372

http://support.microsoft.com/default.aspx?scid=kb;en-us;210372

 

[Ken Snell [MVP]]

Run an update query on the data to convert that "square box" (which most
likely is the line feed character, Chr(10), as EXCEL uses just the line feed
character to put new lines in cells) to the carriage return and line feed
characters (Chr(13) & Chr(10)):

UPDATE Tablename
SET FieldName = Replace([FieldName], Chr(10), Chr(13) & Chr(10), 1, -1, 1);

 

[Guest]

when I pull up the report the square comes up instead of starting a new line
and when i print the square comes up. It is not recognizing the square as a
carrirage control. How do I remove the square box and replace it with a new
line. The reason why the square is there is because in excel the user hit
enter and when i imported it into access it put a little square instead of
starting a new line where the user hit enter.

 

[Guest]

Thanks. When i created the update query and type in
"Replace([FieldName], Chr(10), Chr(13) & Chr(10), 1, -1, 1)" into the
"Update to" field it creates the breaks in the new line. If I run the query
and then go to the table and look at the data, the data looks fine with no
squares and starts a new line. But if i re-run the query again its add a
square. Every time I re-run the query it adds a square. How can I prevent
this.

Run an update query on the data to convert that "square box" (which most
likely is the line feed character, Chr(10), as EXCEL uses just the line feed
character to put new lines in cells) to the carriage return and line feed
characters (Chr(13) & Chr(10)):

UPDATE Tablename
SET FieldName = Replace([FieldName], Chr(10), Chr(13) & Chr(10), 1, -1, 1);
--

Ken Snell
<MS ACCESS MVP>

I have imported data form excel to access. However the data in access
contains a littlet square box representing the carriage return. How do i
replace the little square box with a blank. So when i print the report it
doesn't show the little square box but a space.

 

[Ken Snell [MVP]]

You prevent it by running the query just once!

If you run it again, you now have Chr(13) Chr(10) Chr(10) in the text
string. The second Chr(10) has put you right back where you started, except
that you now have it occurring at the beginning of a new line.

--

Ken Snell
<MS ACCESS MVP>

Thanks. When i created the update query and type in
"Replace([FieldName], Chr(10), Chr(13) & Chr(10), 1, -1, 1)" into the
"Update to" field it creates the breaks in the new line. If I run the query
and then go to the table and look at the data, the data looks fine with no
squares and starts a new line. But if i re-run the query again its add a
square. Every time I re-run the query it adds a square. How can I prevent
this.

Run an update query on the data to convert that "square box" (which most
likely is the line feed character, Chr(10), as EXCEL uses just the line feed
character to put new lines in cells) to the carriage return and line feed
characters (Chr(13) & Chr(10)):

UPDATE Tablename
SET FieldName = Replace([FieldName], Chr(10), Chr(13) & Chr(10), 1, -1, 1);
--

Ken Snell
<MS ACCESS MVP>

I have imported data form excel to access. However the data in access
contains a littlet square box representing the carriage return. How do i
replace the little square box with a blank. So when i print the report it
doesn't show the little square box but a space.