replacing two spaces with one

[jkearns]

I'm having trouble using the Replace function (in my newly installed Access2002) to replace two spaces " " with one " ". This is to correct address style errors on an input form.

Replace is working with my other substring replacements, and I've already tried using a variable string to represent " " just for fun. There's no error--it just doesn't replace the double-spaces.

I can locate the double-spaces using InStr and get this taken care of by looping Do While Not IsNull(InStr(sMyStr, " ")). But I would rather use Replace if possible--it's prettier code, and I can't imagine why it will not work. (This reminds me of fighting the " ' ' " " ' "s!)


sTemp = Trim(Me.Address)

sTemp = Replace(sTemp, " street", " st")
sTemp = Replace(sTemp, " lane", " ln")
sTemp = Replace(sTemp, " road", " rd")
.....
sTemp = Replace(sTemp, " ", " ")

 

[jkearns]

uh, okay.

Well, my double-space thingies appeared in that last post as single-spaces, but I really do have two spaces between the quotes in my code. . . .

 

[Lierduh]

html treats multiple spaces and line feeds etc with only one space. If you want to show two spaces in your post. Try to use:

for one space.

eg. '  ' is made up by:

'  '

' ' should only have one.

As for the replace, have you tried searching for the equivalent ascii code? eg. chr(32) is Space.

 

[Tim K.]

Check this Removing extra spaces out.

 

[raskew]

Try this:

Function onespace(pStr As String)
'*******************************************
'Name:      onespace (Function)
'Purpose:   Removes excessive spaces from a string
'Inputs:    ? onespace(" the    quick    brown fox")
'Output:    "the quick brown fox"
'*******************************************

Dim strHold As String
strHold = RTrim(pStr)
Do While InStr(strHold, "  ") > 0
  strHold = Left(strHold, InStr(strHold, "  ") - 1) & Mid(strHold, InStr(strHold, "  ") + 1)
Loop
onespace = Trim(strHold)
End Function

 

[jkearns]

Thanks raskew. Your solution worked fine.