error type mismatch with carriage return and line feed
<p>I wrote a function to receive a string and return it with all whitespaces wiped out, except spaces. When spaces are at the beginning or end of the string, or after or before another space they are wiped out as well.</p>

<p><strong>Ex.</strong> <strong>"_a_____a"</strong> will become <strong>"a_a"</strong>. ( "_" represents a space)</p>

<p>I was running the following query:</p>

<pre><code>UPDATE table1
SET field1 = whitespace(field1)
</code></pre>

<p>For some reason, when the string starts with a Carriage Return or Line Feed, I receive a <strong>Type mismatch</strong> error. </p>

<pre><code>Public Function whiteSpace(ByVal field As String) As String
    Dim i As Integer
    If (IsNull(field)) Then
        field = ""
        GoTo catchNulls
    End If
    field = RegexReplace(field, "(?=\s)[^ ]", " ")
    field = Trim(field)
    field = RegexReplace(field, "  +", " ")

catchNulls:
    whiteSpace = field
End Function 

Function RegexReplace(ByVal text As String, _
                      ByVal replaceWhat As String, _
                      ByVal replaceWith As String, _
                      Optional ByVal ignoreCase As Boolean = False) As String
    On Error GoTo catch
    Dim RE As Object
    Dim str As String

    str = Empty
    Set RE = CreateObject("vbscript.regexp")
    RE.ignoreCase = ignoreCase
    RE.pattern = replaceWhat
    RE.Global = True
    str = RE.Replace(text, replaceWith)
continue:
    RegexReplace = str
    Exit Function
catch:
    Call raiseError(Err.Number, Err.Source, Err.Description)
    GoTo continue
End Function
</code></pre>

<p>I tried exchanging <code>Public Function whiteSpace(ByVal field As String) As String</code> for <code>Public Function whiteSpace(ByVal field As Variant) As String</code> and I didn't get the error, but fields with Carriage Return and Line Feed stayed the same.</p>