Removing carriage returns from Memo fields

[Guest]

Hi,

I need to export data from a memo field to a tab delimited text file.
But I need to strip out the carriage returns in the memo field. Any
suggestions on how to do this?

I don't see the characters in the text file so it's I don't know how to
tell if they are gone (I tried the Replace function).

Thanks in Advance.

 

[Douglas J. Steele]

Replace(TextToChange, vbCrLf, " ")

 

[Guest]

The constant "vbCRLf" doesn't work in an update query but I wrote up a little
bit of code and that took care of it. Thanks!

My code is far from good but in case it will someone else...

rs.MoveFirst
Do
With rs
If Not IsNull(rs![comments]) Then
.Edit
![comments] = Replace(rs![comments], vbCrLf, " ")
.Update
End If
End With

rs.MoveNext
If rs.EOF Then
Exit Do
End If
Loop

 

[Ken Snell \(MVP\)]

Use
Chr(13) & Chr(10

in place of vbCrLf in the query.

--

Ken Snell
<MS ACCESS MVP>

The constant "vbCRLf" doesn't work in an update query but I wrote up a
little
bit of code and that took care of it. Thanks!

My code is far from good but in case it will someone else...

rs.MoveFirst
Do
With rs
If Not IsNull(rs![comments]) Then
.Edit
![comments] = Replace(rs![comments], vbCrLf, " ")
.Update
End If
End With

rs.MoveNext
If rs.EOF Then
Exit Do
End If
Loop

Replace(TextToChange, vbCrLf, " ")

 

[Douglas J. Steele]

Yeah, as Ken points out, in SQL, you can't use intrinsic constants such as
vbCrLf. You need to use Chr(13) & Chr(10) in that order.

Note that it's almost always considerably faster to use an UPDATE statement
in SQL than to loop through a recordset.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

The constant "vbCRLf" doesn't work in an update query but I wrote up a
little
bit of code and that took care of it. Thanks!

My code is far from good but in case it will someone else...

rs.MoveFirst
Do
With rs
If Not IsNull(rs![comments]) Then
.Edit
![comments] = Replace(rs![comments], vbCrLf, " ")
.Update
End If
End With

rs.MoveNext
If rs.EOF Then
Exit Do
End If
Loop

Replace(TextToChange, vbCrLf, " ")