Removing carriage returns in middle of cell - Access 2000

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to export an Access 2000 table to a comma-delimited text file.
The data originally came into Access thru various Excel files. I am guessing
that Alt-Enter was used in Excel to have multiple lines in one cell. This
action is brought into Access during the import and shows up in the exported
text file. As a result, a given Access record is separated into multiple
lines. How do I remove this "carriage return" so that the record stays on
one line?
 
Use a query to export the data from the table. Put all fields on the query
except the one you want to modify. In its place, use a calculated field that
modifies the data for output.

If your ACCESS 2000 is fully patched, you can use a calculated field similar
to this to replace the CrLf characters with an empty space:

OutField: Replace([Fieldname], Chr(13) & Chr(10), " ", 1, -1, 1)
 
Thanks Ken, but apparently I made a mistake. It seems the symbol I am
encountering is not a carriage return although it does start another line
when imported into a text file.
The symbol looks like the following: .
How do you represent this symbol?

George

Ken Snell said:
Use a query to export the data from the table. Put all fields on the query
except the one you want to modify. In its place, use a calculated field that
modifies the data for output.

If your ACCESS 2000 is fully patched, you can use a calculated field similar
to this to replace the CrLf characters with an empty space:

OutField: Replace([Fieldname], Chr(13) & Chr(10), " ", 1, -1, 1)

--

Ken Snell
<MS ACCESS MVP>


gtvarghese said:
I am trying to export an Access 2000 table to a comma-delimited text file.
The data originally came into Access thru various Excel files. I am guessing
that Alt-Enter was used in Excel to have multiple lines in one cell. This
action is brought into Access during the import and shows up in the exported
text file. As a result, a given Access record is separated into multiple
lines. How do I remove this "carriage return" so that the record stays on
one line?
Click to expand...
Click to expand...
 
Tht symbol by itself does not tell you what it is. It's a "generic"
representation of any character that cannot be displayed. You have to find
out what it is in order to be able to extract/remove/replace it.

If it causes a new line in a text file, then it likely is the line feed
character, Chr(10). This also is the character that Excel uses to make a new
line in a cell.

--

Ken Snell
<MS ACCESS MVP>

gtvarghese said:
Thanks Ken, but apparently I made a mistake. It seems the symbol I am
encountering is not a carriage return although it does start another line
when imported into a text file.
The symbol looks like the following: .
How do you represent this symbol?

George

Ken Snell said:
Use a query to export the data from the table. Put all fields on the query
except the one you want to modify. In its place, use a calculated field that
modifies the data for output.

If your ACCESS 2000 is fully patched, you can use a calculated field similar
to this to replace the CrLf characters with an empty space:

OutField: Replace([Fieldname], Chr(13) & Chr(10), " ", 1, -1, 1)

--

Ken Snell
<MS ACCESS MVP>


gtvarghese said:
I am trying to export an Access 2000 table to a comma-delimited text file.
The data originally came into Access thru various Excel files. I am guessing
that Alt-Enter was used in Excel to have multiple lines in one cell. This
action is brought into Access during the import and shows up in the exported
text file. As a result, a given Access record is separated into multiple
lines. How do I remove this "carriage return" so that the record stays on
one line?
Click to expand...
Click to expand...
Click to expand...
 
Thanks Ken. You have been very helpful.

Ken Snell said:
Tht symbol by itself does not tell you what it is. It's a "generic"
representation of any character that cannot be displayed. You have to find
out what it is in order to be able to extract/remove/replace it.

If it causes a new line in a text file, then it likely is the line feed
character, Chr(10). This also is the character that Excel uses to make a new
line in a cell.

--

Ken Snell
<MS ACCESS MVP>

gtvarghese said:
Thanks Ken, but apparently I made a mistake. It seems the symbol I am
encountering is not a carriage return although it does start another line
when imported into a text file.
The symbol looks like the following: .
How do you represent this symbol?

George

Ken Snell said:
Use a query to export the data from the table. Put all fields on the query
except the one you want to modify. In its place, use a calculated field that
modifies the data for output.

If your ACCESS 2000 is fully patched, you can use a calculated field similar
to this to replace the CrLf characters with an empty space:

OutField: Replace([Fieldname], Chr(13) & Chr(10), " ", 1, -1, 1)

--

Ken Snell
<MS ACCESS MVP>


I am trying to export an Access 2000 table to a comma-delimited text file.
The data originally came into Access thru various Excel files. I am
guessing
that Alt-Enter was used in Excel to have multiple lines in one cell. This
action is brought into Access during the import and shows up in the
exported
text file. As a result, a given Access record is separated into multiple
lines. How do I remove this "carriage return" so that the record stays on
one line?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top