• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2079
  • Last Modified:

Using Trim Function in Echo remove a blank in the middle of a string

Is there a way using the Trim function in Access to remove a space.  In the following address field which is coming from a packaged field.  It looks like this:
                         Manhasset, NY  11030-1945.  

I would like to remove one of the spaces between NY and 11030-1945 without bringing each field in separately and concatanating them together if possible.
1 Solution
Rey Obrero (Capricorn1)Commented:


You can replace 2 spaces with 1 if that's what you mean - it will look through the whole string though...
replace(fieldname, "  ", " ")
moriniaAdvanced Analytics AnalystAuthor Commented:
Is there a way to put this into a query or macro so it can be automated?  I
Rey Obrero (Capricorn1)Commented:
create an update query

update MyTable
set [Myfield]=replace([myfield],space(2),space(1))
Patrick MatthewsCommented:
Hello morinia,

The Access and VBA Trim() function only removes leading and trailing spaces.  Excel's TRIM()
function does that as well as remove excess interior spaces, but it would be excessive to
instantiate an Excel object just for that.

cap1 and Peter have you squared away if you only need to worry about two spaces.  If you
might have additional spaces (say, three or more consecutive), then I would add this UDF:

Function RegExpReplace(LookIn As String, PatternStr As String, Optional ReplaceWith As String = "", _
    Optional ReplaceAll As Boolean = True, Optional MatchCase As Boolean = True)

    ' This function uses Regular Expressions to parse a string, and replace parts of the string
    ' matching the specified pattern with another string.  The optional argument ReplaceAll controls
    ' whether all instances of the matched string are replaced (True) or just the first instance (False)
    ' By default, RegExp is case-sensitive in pattern-matching.  To keep this, omit MatchCase or
    ' set it to True
    ' If you use this function from Excel, you may substitute range references for all the arguments
    Dim RegX As Object
    Set RegX = CreateObject("VBScript.RegExp")
    With RegX
        .Pattern = PatternStr
        .Global = ReplaceAll
        .IgnoreCase = Not MatchCase
    End With
    RegExpReplace = RegX.Replace(LookIn, ReplaceWith)
    Set RegX = Nothing
End Function

and then use it like this:

UPDATE YourTable
SET YourField = RegExpReplace([YourField], " {2,}", " ")


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now