Link breaks on export

[Ray]

I am enterring data in a table through a form. Sometimes I enter a line
break by pressing enter or if a line break exists if I copy and paste data
from another document.

The problem is that these line breaks are recorded when the data is exported
to a text file. I need all the data from one field to appear on online line
of an exported text file rather than breaking to new lines when it is
exported.

Is there a way to do this?

Thanks very much.

Raphael Neff
The Chess House
www.chesshouse.com
(e-mail address removed)

 

[John Nurick]

Hi Raphael,

Yes, it can be done, though there can be some complications.

If the field in question is a text field, you can create a query that
replaces the linebreaks with spaces and then just export the query to
textfile instead of the table.

In Access 2002 or later, you can use the VBA Replace() function in a
calculated field in a query, like this, where XXX is the name of the
field:
fXXX: Replace([XXX], Chr(13) & Chr(10), " ")

In most installations of Access 2000 it's not possible to use Replace()
in a query and you have to create a custom "wrapper" function in a
module in the database, like this:
Public Function ReplaceLineBreaks(V As Variant) As Variant
If IsNull(V) Then
ReplaceLineBreaks = Null
Else
ReplaceLineBreaks = Replace(CStr(V), Chr(13) & Chr(10), " ")
End If
End Function
Then use a calculated field in the query like this:
fXXX: ReplaceLinebreaks([XXX])


If the field is a memo field, it's simplest to delete the line breaks
from the stored data before you export it, by using a calculated field
as above in an Update query.

 

[Ray]

Thank-you so much ! That solved it!

--
Raphael Neff
The Chess House
www.chesshouse.com

Hi Raphael,

Yes, it can be done, though there can be some complications.

If the field in question is a text field, you can create a query that
replaces the linebreaks with spaces and then just export the query to
textfile instead of the table.

In Access 2002 or later, you can use the VBA Replace() function in a
calculated field in a query, like this, where XXX is the name of the
field:
fXXX: Replace([XXX], Chr(13) & Chr(10), " ")

In most installations of Access 2000 it's not possible to use Replace()
in a query and you have to create a custom "wrapper" function in a
module in the database, like this:
Public Function ReplaceLineBreaks(V As Variant) As Variant
If IsNull(V) Then
ReplaceLineBreaks = Null
Else
ReplaceLineBreaks = Replace(CStr(V), Chr(13) & Chr(10), " ")
End If
End Function
Then use a calculated field in the query like this:
fXXX: ReplaceLinebreaks([XXX])


If the field is a memo field, it's simplest to delete the line breaks
from the stored data before you export it, by using a calculated field
as above in an Update query.

I am enterring data in a table through a form. Sometimes I enter a line
break by pressing enter or if a line break exists if I copy and paste data
from another document.

The problem is that these line breaks are recorded when the data is exported
to a text file. I need all the data from one field to appear on online line
of an exported text file rather than breaking to new lines when it is
exported.

Is there a way to do this?

Thanks very much.

Raphael Neff
The Chess House
www.chesshouse.com
(e-mail address removed)