Removing carriage returns in middle of cell - Access 2000

[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?

 

[Ken Snell [MVP]]

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)

 

[Guest]

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

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?

 

[Ken Snell [MVP]]

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>

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

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?

 

[Guest]

Thanks Ken. You have been very helpful.

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>

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

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?