Stripping Out Carriage Returns

  • Thread starter Thread starter Gina
  • Start date Start date
G

Gina

Hi,
I have data that I'm copying into my memo field that
unfortunately has carrriage returns at the end of every
line. When I report it out the carriage return makes it
break not where I want it. Is there any way to strip the
field of carriage returns? I was thinking maybe a
function within a query. Any advice would be very
appreciated!
Gina
 
Assuming you're using Access 2000 or newer, you can use the Replace function
to replace the carriage returns with blanks.

If they're working in Access, you must have a carriage return and a line
feed at the end of each row, not just a carriage return, so you'd need
something like:

Replace([MyMemoField], Chr(13) & Chr(10), " ")

If you're doing this through code (as opposed to through a query), you
should be able to use vbCrLf rather than Chr(13) & Chr(10)
 
Caution: Replace didn't make it into the JET expression service in Access
2000. If you're creating a query in Access 2000 that needs to do replace,
you need to create a public "wrapper" function.

Public Function MyReplace(str1 As String, str2 As String, str3 As String) As
String
MyReplace = Replace(str1, str2, str3)
End Function

... now call "MyReplace" from your queries.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Douglas J. Steele said:
Assuming you're using Access 2000 or newer, you can use the Replace function
to replace the carriage returns with blanks.

If they're working in Access, you must have a carriage return and a line
feed at the end of each row, not just a carriage return, so you'd need
something like:

Replace([MyMemoField], Chr(13) & Chr(10), " ")

If you're doing this through code (as opposed to through a query), you
should be able to use vbCrLf rather than Chr(13) & Chr(10)

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Gina said:
Hi,
I have data that I'm copying into my memo field that
unfortunately has carrriage returns at the end of every
line. When I report it out the carriage return makes it
break not where I want it. Is there any way to strip the
field of carriage returns? I was thinking maybe a
function within a query. Any advice would be very
appreciated!
Gina
Click to expand...
Click to expand...
 
Back
Top