How can I remove hard returns (CR + LF) in a memo field string?

[MLH]

I use A97. I want to remove carriage return / linefeed pairs
from a memo field. How can I strip a string of these special,
non-printing characters?

[fredg]

On Fri, 19 Aug 2005 14:43:01 -0400, MLH wrote:

I use A97. I want to remove carriage return / linefeed pairs
from a memo field. How can I strip a string of these special,
non-printing characters?

In Access 97 you'll need to write a User Defined function.

Copy and Paste the following into a Module

Change the Error Handling as needed.

Function StripReturns(StrIn As String) As String

On Error GoTo Err_Handler

Dim intX As Integer
Dim strName As String
Dim strNewName As String
Dim strY As String

strName = StrIn
For intX = 1 To Len(strName)
strY = Mid(strName, intX, 1)
If (Asc(strY) <> 13 And Asc(strY) <> 10) Then
strNewName = strNewName & strY
End If
Next intX
StripReturns = strNewName

Exit_StripReturns:
Exit Function

Err_Handler:
MsgBox "Error #: " & Err.Number & " " & Err.Description
Resume Exit_StripReturns
End Function
============

Call it from a query:
NewColumn:IIf(Not IsNull([FieldName]),StripReturns([FieldName]),"")

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

[MLH]

Thx FredG
xxxxxxxxxxxxxxxxxxxx

On Fri, 19 Aug 2005 12:34:06 -0700, fredg <fg******@example.invalid>
wrote:

On Fri, 19 Aug 2005 14:43:01 -0400, MLH wrote:

I use A97. I want to remove carriage return / linefeed pairs
from a memo field. How can I strip a string of these special,
non-printing characters?

In Access 97 you'll need to write a User Defined function.

Copy and Paste the following into a Module

Change the Error Handling as needed.

Function StripReturns(StrIn As String) As String

On Error GoTo Err_Handler

Dim intX As Integer
Dim strName As String
Dim strNewName As String
Dim strY As String

strName = StrIn
For intX = 1 To Len(strName)
strY = Mid(strName, intX, 1)
If (Asc(strY) <> 13 And Asc(strY) <> 10) Then
strNewName = strNewName & strY
End If
Next intX
StripReturns = strNewName

Exit_StripReturns:
Exit Function

Err_Handler:
MsgBox "Error #: " & Err.Number & " " & Err.Description
Resume Exit_StripReturns
End Function
============

Call it from a query:
NewColumn:IIf(Not IsNull([FieldName]),StripReturns([FieldName]),"")