Replace random number of spaces in string 1

[Sunwriter]

Hello:

Envrioment: Access 2002

I have a field with data similar to the following:

STUCK AT 55 TEMP N/INT N

Notice the spaces between 55 and TEMP. I would like to elimitate the random number of spaces and replace with 2 spaces such as:

STUCK AT 55 TEMP N/INT N

Thanks in advance for any assistance,

Jeff

 

[Golom]

Try this UDF

Public Function DropSpaces(s As String, _
       Optional MinSpaces As Integer = 2) As String

DropSpaces = s
Do Until Len(DropSpaces) = _
         Replace(DropSpaces, Space(MinSpaces + 1), Space(MinSpaces))
   DropSpaces = _
   Replace(DropSpaces, Space(MinSpaces + 1), Space(MinSpaces ))
Loop

End Function

And call it with

NewString = DropSpaces(SomeString)

or in an SQL statement as

Select DropSpaces([SomeField]), ...

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[Golom]

Sorry ... that should have been

Public Function DropSpaces(s As String, _
       Optional MinSpaces As Integer = 2) As String

DropSpaces = s
Do Until Len(DropSpaces) = _
         [COLOR=red]Len([/color]Replace(DropSpaces, Space(MinSpaces + 1), Space(MinSpaces))[COLOR=red])[/color]
   DropSpaces = _
   Replace(DropSpaces, Space(MinSpaces + 1), Space(MinSpaces ))
Loop

End Function

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]