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

[MLH]

SELECT Adresses.RawDat a
FROM Adresses
WHERE (((Adresses.Raw Data)=InStr(1,[RawData],Chr$(13))));

Its not doing the trick for me and I'm not sure why.

[Salad]

MLH wrote:

SELECT Adresses.RawDat a
FROM Adresses
WHERE (((Adresses.Raw Data)=InStr(1,[RawData],Chr$(13))));

Its not doing the trick for me and I'm not sure why.

That's a very strange filter. It appears you are only looking for
records where RawData is equal to the position where a Chr(13) exists.

Maybe it should be
Where InStr(1,[RawData],Chr$(13)) > 0

[MLH]

<snip>

That's a very strange filter. It appears you are only looking for
records where RawData is equal to the position where a Chr(13) exists.

Maybe it should be
Where InStr(1,[RawData],Chr$(13)) > 0

Yes, I really blew it, Salad. 'preciate the working filter. This has
all come about from my desire to modify code that FredG was
clever enough to create last month. I'm trying to modify it and
a query field that calls it - effectively making the query an
ACTION query which will find and STRIP the hard returns
from a text (or memo) field. Here's the code + query field FredG
gave me...


Function StripReturns(St rIn 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_StripRetur ns:
Exit Function
Err_Handler:
MsgBox "Error #: " & Err.Number & " " & Err.Description
Resume Exit_StripRetur ns
End Function
============
Call it from a query:
NewColumn:IIf(N ot IsNull([FieldName]),StripReturns([FieldName]),"")

[David W. Fenton]

MLH <CR**@NorthStat e.net> wrote in
news:20******** *************** *********@4ax.c om:

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

What you're doing there is finding where the CrLf is located and
getting the string before it and concatenating it with the string
after it.

This is actually the basic framework for a generic Replace()
function and you'd just replace the constant vbCrLf with a parameter
passed into your function.

(of course, all the above is air code, so it might break in some way
or the other)

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

[MLH]

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. I do like using
the VBA constant. Gives me the feeling Microsoft's
done some of the work for me.

[David W. Fenton]

MLH <CR**@NorthStat e.net> wrote in
news:qj******** *************** *********@4ax.c om:

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

[Salad]

David W. Fenton wrote:

MLH <CR**@NorthStat e.net> wrote in
news:qj******** *************** *********@4ax.c om:

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
StripReturn s = 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. I find your points valid. Sometimes, when programming, the
developer follows a code and style they are working with and familiar
with. Because of that structure, they code differently that you do. As
far as I am concered, that's the magnificance of the mind. One person
could do something one way, another could return the same results using
a completely different method.

If I could go back to my application and rewrite it, it would be far
more effecient and "elegant" than the code that exists. But I need to
do things, inherited an app that had requirement now, and so now it is a
real fine app but there are some things about it that could be done
better.

If I had a team to look over code, then things can be done more
efficiently. Or I could test a couple of different methods to perform
something and see if one thing beats the by a milisecond, but I don't
have the time.

I need to use an example. I would use more "withs" in my code
structure...if I had the time to remember "with". I'm used to my style,
others to their's, and the way I look at it, if it works that's better
than something that doesn't work.

Do keep with the suggestions on methods to do things better. I simply
think you could do it with a less abrasive style.