Need routine to parse spaces out text fld 4

[Dylan]

I have a text fld with spaces embedded that I need to remove and reformat.

Example of input text:

"NSF FOR Account 123 "


I would like to parse into (multiple spaces are removed)

"NSF FOR Account 123"

note each set of text is just seperated by 1 space in the desired result.

Thanks for any help !!! [sig][/sig]

 

[MichaelRed]

Dylan,

Public Function basTrimSpace(StrIn As String) As String

Dim Wds() As String
Dim InWord As Boolean
Dim LastChar As String
Dim MyChar As String
Dim Idx As Integer
Dim Jdx As Integer

ReDim Wds(0) 'Start An Array of Words
InWord = True 'Assume the First Character in the Input is NOT a space

For Idx = 1 To Len(StrIn)

MyChar = Mid(StrIn, Idx, 1)
If (InWord And (MyChar = " &quot) Then
InWord = False
Jdx = Jdx + 1
ReDim Preserve Wds(Jdx)
End If

If (MyChar <> &quot; &quot Then
InWord = True
Wds(Jdx) = Wds(Jdx) & MyChar
End If

Next Idx

For Idx = 0 To UBound(Wds)
basTrimSpace = basTrimSpace & Wds(Idx) & &quot; &quot;
Next Idx

basTrimSpace = Trim(basTrimSpace)

End Function
[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

 

[THoey]

This will give you another way to try it. Not saying that this is a better way of doing it. Just happened to start on it before lunch, went to lunch, finished it and then saw that Michael had answered already.

This way just loops through a table reading a field, parses the extra spaces out and then stores the result in a second field. Use which ever way is best for you and your app.

Public Function RemoveSpaces()
Dim db As Database
Dim rst As Recordset
Dim stringin As String
Dim pos As Integer

Set db = CurrentDb
Set rst = db.OpenRecordset(&quot;select * from table1&quot

rst.MoveFirst

Do While Not rst.EOF

rst.Edit
stringin = rst!string1

Do While InStr(1, stringin, &quot; &quot
pos = InStr(1, stringin, &quot; &quot
stringin = Left(stringin, pos - 1) & &quot; &quot; & Mid(stringin, pos + 2)
Loop

rst!string2 = stringin
rst.Update
rst.MoveNext

Loop

End Function


Hope it helps... [sig]<p>Terry M. Hoey<br><a href=mailto:th3856@txmail.sbc.com>th3856@txmail.sbc.com</a><br><a href= > </a><br>Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?[/sig]

 

[hawg]

Access 2000
I'm looking for a routine to remove spaces in a text field.
Example: 52 36 89 becomes 523689.
The spaces do appear in the same place in each record.
I'm familiar with the basics of SQL, but no other languages.
Thanks in advance for any help you can offer.

 

[JerryDennison]

If you ABSOLUTELY certain the spaces occur in the EXACT same position you can use the following in a query(update or otherwise).

Left([FieldName],1) & Mid([FieldName], 4,2) & Right([FieldName], 2)

 

[MichaelRed]

I posted a routine (some time ago) in the FAQ &quot;basSplit&quot;. It will place each delimited string in an array. If you use the &quot;Space&quot; character as the delimiter, it will return an array with each Group &quot;52&quot;, &quot;36&quot;, &quot;89&quot; in a seperate array element. You can 'reassemble' the groups as a contigious character string with a simple loop.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[rafe]

???

Cut & paste from Access 2K help on Trim() function...
=======
TRIM
Removes all spaces from a text string except for single spaces between words.

Syntax

TRIM(text)

Text is the text you want spaces removed from.
=======

Why doesn't this do what Dylan wants?

Unless Dylan is using space delimited fields in records I don't see the need for a complicated routine here

 

[MichaelRed]

rafe,

read your own post

except for single spaces between words.

He wants those removed!


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[rafe]

oops!

you're right & i'm wrong. i read that as &quot;leaving one space between words&quot;

not what it really says & not what it does.

sorry

 

[BrianB]

How about

Replace(Text,&quot; &quot;,&quot; &quot

That is, replace any instance of two spaces with one.
I use this kind of thing all the time.

-Brian

Brian Begy
Developer
Chicago Data Solutions

http://www.chicagodatasolutions.com

brian@chicagodatasolutions.com

 

[hennep]

Access 97 does not support the Replace function.
In Access2000 you'll need to repeat the replace function.

This is the shortest code so far, but is it also fast ?

do while instr(text,&quot; &quot > 0
text = Replace(text,&quot; &quot;,&quot; &quot
loop

 

[MichaelRed]

Terry M. Hoey,

Your approach (post of 9/13/2000) is interesting. I was interested enough to generlaize it to Accept user input for several elements. It Appears to be at least competive - in execution time to other approaches - at least for the single field 'correction'.

Public Function basRmvDupChr(TblName As String, _
                             FrmFld As String, _
                             ToFld As String, _
                             Optional RmvChr As Variant = " ")

    'Usage:
    'basRmvDupChr("tblNames", "FullName", "FirstName")      //Remove Multiple Spaces
    'basRmvDupChr("tblNames", "FullName", "FirstName", "g") //Remove Multiple "g"s

    Dim Dbs As Database
    Dim Rst As Recordset
    Dim strIn As String
    Dim Pos As Integer

    Set Dbs = CurrentDb
    Set Rst = Dbs.OpenRecordset("select * from " & TblName)

    Rst.MoveFirst

    RmvChr = Left(RmvChr, 1)

    Do While Not Rst.EOF

        strIn = Rst.Fields(FrmFld)

        Pos = InStr(1, strIn, RmvChr & RmvChr)
        Do While Pos
            strIn = Left(strIn, Pos - 1) & RmvChr & Mid(strIn, Pos + 2)
            Pos = InStr(1, strIn, RmvChr & RmvChr)
        Loop

        Rst.Edit
        Rst.Fields(ToFld) = strIn
        Rst.Update
        Rst.MoveNext

    Loop

End Function

MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[THoey]

Thanks Michael, I like the generic version you have created. Could be used anywhere, for any replacement.

BTW, kinda seems weird seeing this thread come back to life after five months. Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

 

[MichaelRed]

&quot;BTW, kinda seems weird seeing this thread come back to life after five months. &quot;

Not just weird, also somewhat confusing. I KNEW I had answered. Took a second to realize someone was 'piggbacking' on the old thread.

Since I'm now &quot;On the Dole&quot; (not &quot;GAINFULLY&quot employed, I have/spend way to much time looking at these postings.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over