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

Posted on 2007-10-15
Last Modified: 2008-05-13
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.
Question by:morinia
    LVL 119

    Expert Comment

    by:Rey Obrero


    LVL 77

    Expert Comment

    You can replace 2 spaces with 1 if that's what you mean - it will look through the whole string though...
    replace(fieldname, "  ", " ")

    Author Comment

    Is there a way to put this into a query or macro so it can be automated?  I
    LVL 119

    Accepted Solution

    create an update query

    update MyTable
    set [Myfield]=replace([myfield],space(2),space(1))
    LVL 92

    Expert Comment

    by:Patrick Matthews
    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,}", " ")



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now