access97 parsing out enter and tab spaces in fields.

[JMCN]

does anyone know how to take out {enter} strings from a field?
for instance, i have:
"INTWEST ADJUSTMENT
ORG ILLINOIS FUND PARTICIPANT DIVIDEND".

i managed to take out extra spaces but then it
comes to {enter} and {tab} i cannot parse them out.

thanks!! jung

Dim x As Integer

Set dbs = CurrentDb
' Open recordset on tblLiveWire table.
Set rst = dbs.OpenRecordset("qryLiveWire", dbOpenDynaset)

rst.MoveFirst
Do Until rst.EOF

Do While InStr(rst![source_bank_name].Value, " ") <> 0
rst.Edit
x = InStr(rst![source_bank_name].Value, " ")

rst![source_bank_name].Value =
left(rst![source_bank_name].Value, x - 1) &
Mid(rst![source_bank_name].Value, x + 1,
Len(rst![source_bank_name].Value) - x)
rst.Update
Loop

rst.MoveNext

Loop

End Function

[Mike Storr]

On 30 Jan 2004 13:50:52 -0800, JMCN wrote:

does anyone know how to take out {enter} strings from a field?
for instance, i have:
"INTWEST ADJUSTMENT
ORG ILLINOIS FUND PARTICIPANT DIVIDEND".

i managed to take out extra spaces but then it
comes to {enter} and {tab} i cannot parse them out.

thanks!! jung

Dim x As Integer

Set dbs = CurrentDb
' Open recordset on tblLiveWire table.
Set rst = dbs.OpenRecordset("qryLiveWire", dbOpenDynaset)

rst.MoveFirst
Do Until rst.EOF

Do While InStr(rst![source_bank_name].Value, " ") <> 0
rst.Edit
x = InStr(rst![source_bank_name].Value, " ")

rst![source_bank_name].Value =
left(rst![source_bank_name].Value, x - 1) &
Mid(rst![source_bank_name].Value, x + 1,
Len(rst![source_bank_name].Value) - x)
rst.Update
Loop

rst.MoveNext

Loop

End Function

Both the Enter key and the Tab key have character codes that can be used to
search for. If you check the help files for ASCII Codes you see what they
are. The easiest way is use a Do Until...Loop. Within this loop, use
InStr() to find the desired charcter. This will indicate it's posistion in
the string if found, 0 if not. If found, assign to a variable the portion
of the string up to the posistion returned, and to another variable,
everything after. Concatenate these two halfs back together and reassign to
the orignal string being searched on. Continue this loop until InStr()
returns 0. All instances of the character looked for should now be removed.
Be careful though. If you are looking for "extra" uses of these characters,
that will be far more difficult to do accurately.
--
Mike Storr
veraccess.com

[JMCN]

Mike Storr <st******@sympatico.ca> wrote in message news:<1w******************************@40tude.net> ...

On 30 Jan 2004 13:50:52 -0800, JMCN wrote:

does anyone know how to take out {enter} strings from a field?
for instance, i have:
"INTWEST ADJUSTMENT
ORG ILLINOIS FUND PARTICIPANT DIVIDEND".

Both the Enter key and the Tab key have character codes that can be used to
search for. If you check the help files for ASCII Codes you see what they
are. The easiest way is use a Do Until...Loop. Within this loop, use
InStr() to find the desired charcter. This will indicate it's posistion in
the string if found, 0 if not. If found, assign to a variable the portion
of the string up to the posistion returned, and to another variable,
everything after. Concatenate these two halfs back together and reassign to
the orignal string being searched on. Continue this loop until InStr()
returns 0. All instances of the character looked for should now be removed.
Be careful though. If you are looking for "extra" uses of these characters,
that will be far more difficult to do accurately.

thanks for the advise!! i ended up finding the chr(13) that represents
the "enter". here is the new code but i'm having problems stepping
into the
do while statement. what am i doing wrong?
thanks again for any help.

Set dbs = CurrentDb
' Open recordset on tblLiveWire table.
Set rst = dbs.OpenRecordset("qryLiveWire", dbOpenDynaset)

rst.MoveFirst
Do Until rst.EOF
x = InStr(rst![wire_text].Value, chr(13))
Do While InStr(rst![wire_text].Value, chr(13)) <> 0
rst.Edit
'x = InStr(rst![wire_text].Value, chr(13))
rst![wire_text].Value = left(rst![wire_text].Value, x - 1) &
Mid(rst![wire_text].Value, x + 1, Len(rst![wire_text].Value) - x)
rst.Update
Loop
rst.MoveNext
Loop

[Wayne Gillespie]

On 2 Feb 2004 14:01:40 -0800, pi******@yahoo.fr (JMCN) wrote:

Mike Storr <st******@sympatico.ca> wrote in message news:<1w******************************@40tude.net> ...

On 30 Jan 2004 13:50:52 -0800, JMCN wrote:

> does anyone know how to take out {enter} strings from a field?
> for instance, i have:
> "INTWEST ADJUSTMENT
> ORG ILLINOIS FUND PARTICIPANT DIVIDEND".
>

Both the Enter key and the Tab key have character codes that can be used to
search for. If you check the help files for ASCII Codes you see what they
are. The easiest way is use a Do Until...Loop. Within this loop, use
InStr() to find the desired charcter. This will indicate it's posistion in
the string if found, 0 if not. If found, assign to a variable the portion
of the string up to the posistion returned, and to another variable,
everything after. Concatenate these two halfs back together and reassign to
the orignal string being searched on. Continue this loop until InStr()
returns 0. All instances of the character looked for should now be removed.
Be careful though. If you are looking for "extra" uses of these characters,
that will be far more difficult to do accurately.

thanks for the advise!! i ended up finding the chr(13) that represents
the "enter". here is the new code but i'm having problems stepping
into the
do while statement. what am i doing wrong?
thanks again for any help.

Set dbs = CurrentDb
' Open recordset on tblLiveWire table.
Set rst = dbs.OpenRecordset("qryLiveWire", dbOpenDynaset)

rst.MoveFirst
Do Until rst.EOF
x = InStr(rst![wire_text].Value, chr(13))
Do While InStr(rst![wire_text].Value, chr(13)) <> 0
rst.Edit
'x = InStr(rst![wire_text].Value, chr(13))
rst![wire_text].Value = left(rst![wire_text].Value, x - 1) &
Mid(rst![wire_text].Value, x + 1, Len(rst![wire_text].Value) - x)
rst.Update
Loop
rst.MoveNext
Loop

This function will strip Chr(13) and Chr(10) from a string and return a single line string.

'============================================
Function fStripCRLFs(strIn As Variant) As String
Dim strNew As String
Dim x As Integer
Dim y As String

On Error Resume Next

If Len(strIn & "") = 0 Then
fStripCRLFs = ""
Else
strNew = ""
For x = 1 To Len(strIn)
y = Mid(strIn, x, 1)
Select Case y
Case Chr(13)
strNew = strNew & " "
Case Chr(10)
strNew = strNew
Case Else
strNew = strNew & Mid(strIn, x, 1)
End Select
Next x

fStripCRLFs = strNew
End If

On Error GoTo 0

Exit Function
'============================================
Set dbs = CurrentDb
' Open recordset on tblLiveWire table.
Set rst = dbs.OpenRecordset("qryLiveWire", dbOpenDynaset)

If rst.RecordCount<>0 Then
rst.MoveFirst
Do Until rst.EOF
x= fStripCRLFs(rst![wire_text])
If rst![wire_text] <> x Then
rst.Edit
rst![wire_text] = x
rst.Update
End If
rst.MoveNext
Loop
End If

rst.Close
Set rst = Nothing
Wayne Gillespie
Gosford NSW Australia