Removing the Enter from a string in Access 97

[Jekub]

I am trying to remove all enters from a text string and replace them with a comma and a space in Access 97 which means that I can't use the Replace function as that doesn't exist in this version. I am using the following code:

For x = 1 To Len(string)
y = Mid(string, x, 1)
If y = Chr(13) Then
y = ", "
End If
NewString = NewString + y
Next x
string = NewString

The problem is that this code completely fails to detect and remove the enter. What am I doing wrong, any ideas?

 

[boblarson]

If this is in a function check for

vbCrLf

instead of Chr(13).

And instead of going character by character, just use the replace to replace any instances of it.

 

[dcb]

And if its a memo/text field with "enters" try this:

Public Function fRipCrLf( _
                ByVal Str As String, _
                Optional ByVal Char As String _
                ) As String
    If IsNull(Char) Then Char = ""
    
   Str = Replace(Str, Chr(10), Char)
   Str = Replace(Str, Chr(13), "")
       
    fRipCrLf = Str
    
End Function

Public Sub fRipCrLf_Usage_Example()
    Dim Phrase As String, Char As String, CharCode As String
    'Phrase = "This is my vbr string" & vbCrLf & "There is a enter"
    Phrase = "This is my Chr string" & (Chr(10) & Chr(13)) & "There is a enter"
    Debug.Print "Example With vbConst"
    Debug.Print "** " & fRipCrLf(Phrase, vbTab & " - " & vbTab)
    Debug.Print "Example With Character"
    Debug.Print "** " & fRipCrLf(Phrase, "@")
    Debug.Print "Example With No Char Decl"
    Debug.Print "** " & fRipCrLf(Phrase)
End Sub

 

[ChrisO]

Or you could try this: -

Sub TestIt()
    Dim lngX      As Long
    Dim strString As String
    
    strString = Chr$(13) & Chr$(10) & "ABC" & Chr$(13) & Chr$(10) & "DEF" & Chr$(13) & Chr$(10) & "..."
    
    MsgBox strString
    
    For lngX = 1 To Len(strString)
        If Mid$(strString, lngX, 2) = Chr$(13) & Chr$(10) Then
            Mid$(strString, lngX, 2) = ", "
        End If
    Next lngX
    
    MsgBox strString
 
End Sub

Depending on where the first and last CR/LF pair are you may also need to remove the ', ' pair from the start/end.