Re: Will this SQL work to find records with hard returns in one of the text fields?

MLH <CRCI@xxxxxxxxxxxxxx> wrote in
news:qj1rk1ti34dvk0dsh3qh5ghmrpf4ln9nsf@xxxxxxx:

>>> 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
>>
>>This is just ridiculously complex code.
>>
>>There's a VBA constant, vbCrLf, that you can use for this, and use
>>Instr to find the location of that 2-character string:
>>
>> Dim lngPosition As Long
>>
>> Do Until InStr(strTemp, vbCrLf) = 0
>> lngPosition = InStr(strTemp, vbCrLf)
>> If lngPosition = 0 Then
>> Exit Do
>> Else
>> strTemp = Mid(strTemp, 1, lngPosition - 1)
>> strTemp = strTemp & Mid(strTemp, lngPosition+2)
>> End If
>> Loop
>
> I can see your point. But one's 8-lines and the other's
> 9-lines = not a huge difference there. . . .

No, mine is 10 lines and yours is 12.

Mine is also faster, as it doesn't walk through the string character
by character, and has fewer calls to other functions. Mine could
also be reduced to 9 lines by combining the strTemp concatenations
into one line. Of course, yours could be reduced by two lines by
putting your If statement on one line.

> . . . I do like using
> the VBA constant. Gives me the feeling Microsoft's
> done some of the work for me.

That's what the constants are for.

My point is that you don't need to walk through the string character
by character, which is what I considered "convoluted" when you are
dealing with replacing a 2-character string.

And, of course, becasue of that, yours is not generalizable to
replacing any string of any length, precisely because it's assuming
two characters.

Mine also assumes 2 characters, but that can be fixed with this
change:

strTemp = strTemp & Mid(strTemp, lngPosition+Len(vbCrLf))

so that you'd replace vbCrLf with the variable you're passing in as
your search string.

Yours really can't be altered to fix that without major
re-engineering.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
.

Relevant Pages

  • Re: Linefeed to CrLf
    ... onlu puts a Lf character at the end of each row. ... Dim s As String ... vbcrlf replace and doing something like: ...
    (comp.databases.ms-access)
  • Re: Query Nightmare!!!
    ... I'm not sure vbCrLf will work in SQL; it is a VBA constant as it begins with ... and ## to delineate string and date data in a SQL string. ... for some reason, the vbCrLf does not create a carriage return, although no ...
    (microsoft.public.access.queries)
  • Re: Linefeed to CrLf
    ... onlu puts a Lf character at the end of each row. ... Function ReadAll(path As String) As String ... Dim s As String ... vbcrlf replace and doing something like: ...
    (comp.databases.ms-access)
  • How to do a line feed in a Text Box
    ... I've tried vbcrlf but that just places the "p" looking carriage return ... character in my string. ...
    (microsoft.public.excel.programming)
  • Re: Warp a String
    ... How to find No. of Lines in String if warp at 41 character or VbCrLf ... Dim LinesAs String ...
    (microsoft.public.vb.winapi)