Access VBA remove CR & LF only from the beginning of a text string by searching for them

I need to remove line breaks from the beginning of a memo 开发者_开发技巧type records. I dont want to use the replace function as it would remove all line breaks from the record which is not desired. Its only the line breaks at the beginning of the field that I am interested in removing.

Furthermore, the my records do not always begin with a line break so I cant really use text positioning, the solution would be to look for line break at the beginning instead of always expecting it at the beginning.

If Len(string) > 0 Then
   Do While Left(string,1)= chr(13) Or Left(string,1)= chr(10) or Left(string,1) = " "
     string = Right(string, len(string)-1)
   Loop
End If

This will check to make sure the string isn't empty, then runs a simple loop to remove the left-most character as long as it is either a CR (chr(13)), LF (chr(10)), or a space (" ").

Once the loop hits the first character that doesn't match the criteria, it stops and you have the desired result of trimming all extra CR, LF, and space characters only from the beginning of the string.

Since it's relatively short, I just put it in the event procedure where needed, you could also modify it to be a public function in a module if you see fit.

Replace does not replace all occurences when you use the count argument: http://office.microsoft.com/en-us/access/HA012288981033.aspx

You can test it like so:

s1 = vbCrLf & "abc"
s2 = "ab" & vbCrLf & "c"

MsgBox "---" & IIf(Left(s1, 2) = vbCrLf, Replace(s1, vbCrLf, "", , 1), s1)
MsgBox "---" & IIf(Left(s2, 2) = vbCrLf, Replace(s2, vbCrLf, "", , 1), s2)

Improving upon what SBinVA wrote

The following code does not need the if statement and it is easy to expand to more character (space, tabs, etc.).

(It also assumes line breaks can originate from a file that can comes from other systems, so vbCr and vbLf are used separately, which takes care of all scenarios.)

Public Function trimCrOrLf(ByVal s As String) As String
  Dim firstChar As String

  firstChar = Left(s, 1)
  Do While InStr(vbCr & vbLf, firstChar) > 0
    s = Mid(s, 2)
    firstChar = Left(s, 1)
  Loop
  trimCrOrLf = s
End Function

Consider a SQL UPDATE statement to discard only those CRLF at the beginning of each memo field.

UPDATE MyTable SET MyTable.memo_field = Mid([memo_field],3)
WHERE (((MyTable.memo_field) Like Chr(13) & Chr(10) & "*"));

Private Sub TestLineFeed()
Dim strString$, strTestChar, booStartsWith_CR As Boolean

strString = Chr$(13) & "some text"

strTestChar = "2"
'strTestChar = Chr$(13)   ''This is a CR.  

booStartsWith_CR = (Left(strString, 1) = strTestChar)

Debug.Print "-----"
Debug.Print "Raw: " & strString
Debug.Print booStartsWith_CR

If booStartsWith_CR Then
    strString = Mid(strString, 2, 100)
End If

Debug.Print "-----"
Debug.Print "New: " & strString
End Sub

Note alternatives for strTestChar so you can see the action. You should notice "-----" in your Immediate Window is followed by a CR, thus a blank line; and this can be removed. Mid(strString, 2, 100) will need some tweaking, but the idea is to copy over your memo string without the first character.

I would use a function like this. It's fairly straight-forward and easily adapted to other circumstances. For example, to remove leading spaces too, add another test to the if (c = vbCr) line.

Function LTrimCRLF(s As String) As String
  Dim index As Integer, start As Integer, strLen As Integer
  Dim c As String

  strLen = Len(s)
  index = 1
  start = -1

  Do While (index <= strLen) And (start = -1)
    c = Mid(s, index, 1)

    If (c = vbCr) Or (c = vbLf) Then
      index = index + 1
    Else
      start = index
    End If
  Loop

  If start = -1 Then
    LTrimCRLF = ""
  Else
    LTrimCRLF = Mid(s, start)
  End If
End Function

Here's a test routine:

Sub TestLTrimCRLF()
  Dim withWS As String, noWS As String, blank As String, onlyWS As String

  withWS = vbCrLf & "  this string has leading white space"
  noWS = "this string has no leading white space"
  onlyWS = vbCrLf & " " & vbCrLf & " "
  blank = ""

  Say "with WS: {" & LTrimCRLF(withWS) & "}"
  Say "no WS:   {" & LTrimCRLF(noWS) & "}"
  Say "only WS: {" & LTrimCRLF(onlyWS) & "}"
  Say "blank:   {" & LTrimCRLF(blank) & "}"
End Sub

BTW, I tried looking at your sample data, but it says the document is not available. Maybe you need to make it public or something?

My contribution to VBA trimwhitespace() function, loop finds for first non-whitespace index, splits a string, then same thing for trailing whitespaces. Left+Right functions are run only once. If you need just leftTrim or rightTrim it's easy to introduce new arguments or separate functions.

Function trimWhitespace(str As String) As String
    Dim idx As Long
    Dim ch As String
    ' LeftTrim
    If Len(str) > 0 Then
        idx = 1
        ch = Mid(str, idx, 1)
        Do While ch = Chr(13) Or ch = Chr(10) Or ch = " "
            idx = idx + 1
            ch = Mid(str, idx, 1)
        Loop
        If (idx > 1) Then str = Right(str, Len(str) - idx)
    End If
    ' RightTrim
    idx = Len(str)
    If idx > 0 Then
        ch = Mid(str, idx, 1)
        Do While ch = Chr(13) Or ch = Chr(10) Or ch = " "
            idx = idx - 1
            ch = Mid(str, idx, 1)
        Loop
        If (idx < Len(str)) Then str = Left(str, idx)
    End If

    trimWhitespace = str
End Function

This will trim all leading and trailing spaces, carriage returns, tabs, and other non-printable characters.

Public Function TrimSpecial(InputString As Variant) As String
' This will trim leading/trailing spaces and non-printable characters from the passed string.

    Dim i As Integer
    Dim str As String

    On Error GoTo ErrorHandler

    str = InputString

    For i = 1 To Len(str)
        If Asc(Mid(str, i, 1)) > 32 And Asc(Mid(str, i, 1)) < 127 Then
            ' Valid character found.  Truncate leading characters before this.
            str = Mid(str, i)
            Exit For
        End If
    Next i

    For i = Len(str) To 1 Step -1
        If Asc(Mid(str, i, 1)) > 32 And Asc(Mid(str, i, 1)) < 127 Then
            ' Valid character found.  Truncate trailing characters after this.
            str = Mid(str, 1, i)
            Exit For
        End If
    Next i

    TrimSpecial = str

Exit_Function:
    Exit Function

ErrorHandler:
    MsgBox "Error #" & Err.Number & " - " & Err.Description & vbCrLf & "in procedure TrimSpecial"
    GoTo Exit_Function
    Resume Next
    Resume

End Function

You can use this routine to test it:

Public Sub Test_TrimSpecial()
    ' Run this to test the TrimSpecial function.
    Dim x As String

    x = vbCrLf & " " & vbTab & " ab cd" & vbCrLf & vbTab & " xyz " & vbCr & vbCrLf
    Debug.Print "-----"
    Debug.Print ">" & x & "<"
    Debug.Print "-----"
    Debug.Print ">" & TrimSpecial(x) & "<"
    Debug.Print "-----"
End Sub 

Like "*" & Chr(13) & Chr(10)

(Access used carriage return + line feed, characters 13 and 10, for a new line).

To remove the carriage return/line feed, change the query to an update query and enter the following in the Update to line:

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

or

Replace([FieldName], Chr(10),"")

Replace([FieldName], Chr(13),"")