Trying to remove extra spaces in an address field

[Realstar1]

I need help. I'm trying to do an update query to delete extra spaces in a field


I want to Change 1234[space][space][space]Elm[space][space] St.

to

1234[space]Elm[space]St.

Thanks,

Vern

 

[statsman]

Are the addresses in the table like this: (3 separate fields)

Number Street Road
1234 Elm St.

Or are they all in one field- represents a space)
Address
1234---Elm--St.

If it is the first example, create a new field in your query

Address[Number]&" "&[Street]&" "&[Type])
This will give you the address with only one space between each item. By the way, don't use the alias "Address" if that term is being used somewhere else.


If its the second example (all in one field) you may have to go into the table and manually remove the spaces. There may be another way to do it but I can't think of it. Before going the manual route, wait a day or two. Someone else may have an idea so you can avoid all the work.

I noticed after posting all of the colons are coming out as purple faces.

 

[raskew]

Hi -

The following function will remove excess spaces from a string:

Function OneSpace(pstr As String) As String

'*******************************************
'Purpose:   Removes excess spaces from a string
'Input:     ? onespace(" now    is  the  time for   all good men  ")
'Output:    "now is the time for all good men"
'*******************************************

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

HTH - Bob

 

[statsman]

See, I told you someone would know.